/* 
Question 1
a. Write a query to fetch the data for a table that will break down the average revenue 
   by country and year
*/
SELECT c.country,t.year,s.revenue
FROM sales_enriched as s JOIN countries as c ON s.retailer_city=c.city
                         JOIN times as t ON t.date=s.date
/*
b. Modify the query to do the aggregation and grouping
*/
SELECT c.country,t.year,sum(s.revenue)
FROM sales_enriched as s JOIN countries as c ON s.retailer_city=c.city
                         JOIN times as t ON t.date=s.date
GROUP BY c.country,t.year
/*
c. Format the result as currency and give it an appropriate heading
*/
SELECT c.country,t.year,format(sum(s.revenue),'C') as revenue
FROM sales_enriched as s JOIN countries as c ON s.retailer_city=c.city
                         JOIN times as t ON t.date=s.date
GROUP BY c.country,t.year
/*
d. Add the grand total to the query, use GROUPING SETS
*/
SELECT c.country,t.year,format(sum(s.revenue),'C') as revenue
FROM sales_enriched as s JOIN countries as c ON s.retailer_city=c.city
                         JOIN times as t ON t.date=s.date
GROUP BY GROUPING SETS ((c.country,t.year),())
/*
e. Add the subtotal for each country to the query by changing the GROUPING SETS
*/
SELECT c.country,t.year,format(sum(s.revenue),'C') as revenue
FROM sales_enriched as s JOIN countries as c ON s.retailer_city=c.city
                         JOIN times as t ON t.date=s.date
GROUP BY GROUPING SETS ((c.country,t.year),(c.country),())
/*
f. (Optional) Do step c. using UNION ALL instead of GROUPING SETS to add the grand total.
Try different ways to display your data.
*/
SELECT c.country,t.year,format(sum(s.revenue),'C') as revenue
FROM sales_enriched as s JOIN countries as c ON s.retailer_city=c.city
                         JOIN times as t ON t.date=s.date
GROUP BY c.country,t.year
UNION ALL
SELECT null,null,format(sum(s.revenue),'C') as revenue
FROM sales_enriched as s JOIN countries as c ON s.retailer_city=c.city
                         JOIN times as t ON t.date=s.date
