Division between data in rows - SQL

Question

The data in my table looks like this:

date, app, country, sales
2017-01-01,XYZ,US,10000
2017-01-01,XYZ,GB,2000
2017-01-02,XYZ,US,30000
2017-01-02,XYZ,GB,1000

I need to find, for each app on a daily basis, the ratio of US sales to GB sales, so ideally the result would look like this:

date, app, ratio
2017-01-01,XYZ,10000/2000 = 5
2017-01-02,XYZ,30000/1000 = 30

I'm currently dumping everything into a csv and doing my calculations offline in Python but I wanted to move everything onto the SQL side. One option would be to aggregate each country into a subquery, join and then divide, such as

select d1_us.date, d1_us.app, d1_us.sales / d1_gb.sales from
(select date, app, sales from table where date between '2017-01-01' and '2017-01-10' and country = 'US') as d1_us
join 
(select date, app, sales from table where date between '2017-01-01' and '2017-01-10' and country = 'GB') as d1_gb
on d1_us.app = d1_gb.app and d1_us.date = d1_gb.date

Is there a less messy way to go about doing this?

Answer 1

You can use the ratio of SUM(CASE WHEN) and GROUP BY in your query to do this without requiring a subquery.

SELECT DATE, 
       APP,
       SUM(CASE WHEN COUNTRY = 'US' THEN SALES ELSE 0 END) /
       SUM(CASE WHEN COUNTRY = 'GB' THEN SALES END) AS RATIO    
FROM TABLE1
GROUP BY DATE, APP;

Based on the likelihood of the GB sales being zero, you can tweak the GB's ELSE condition, maybe ELSE 1, to avoid Divide by zero error. It really depends on how you want to handle exceptions.