USE OLAP
--Question 1 
--a. Write a query that shows the total profit for each country from highest to lowest
--   make sure the profit is formatted as currency
SELECT c.country, format(sum(s.profit),'C') as profit
FROM countries as c JOIN sales_enriched as s ON c.city=s.retailer_city
GROUP BY c.country
ORDER BY profit desc

--b. Display this as a bar graph using REPLICATE and the symbol represeted by decimal 9608.
--   You should display one symbol for every $1,000 in profit
SELECT c.country
       ,replicate(nchar(9608),round(sum(s.profit)/1000,0)) as [profit                                 ]
	   ,FORMAT(sum(s.profit),'C') as value
FROM countries as c JOIN sales_enriched as s ON c.city=s.retailer_city
GROUP BY c.country
ORDER BY profit desc


--Question 2
--a. Generate the necessary data for a pie chart breaking our profit down by country
SELECT c.country
       ,sum(s.profit) as profit
       ,sum(sum(s.profit)) over() as grand_total
	   ,(sum(s.profit)/sum(sum(s.profit)) over())*100 as percent_total
	   ,(sum(s.profit)/sum(sum(s.profit)) over()) as fraction
	   ,(sum(s.profit)/sum(sum(s.profit)) over())*360 as degrees
FROM sales_enriched as s JOIN countries as c ON s.retailer_city=c.city
GROUP BY c.country
--b. Do not worry about display it as a linear_pie_chart. But it might be worth checking the
--totals
WITH pie_graph_data AS
	(SELECT c.country
		   ,sum(s.profit) as profit
		   ,sum(sum(s.profit)) over() as grand_total
		   ,(sum(s.profit)/sum(sum(s.profit)) over())*100 as percent_total
		   ,(sum(s.profit)/sum(sum(s.profit)) over()) as fraction
		   ,(sum(s.profit)/sum(sum(s.profit)) over())*360 as degrees
	FROM sales_enriched as s JOIN countries as c ON s.retailer_city=c.city
	GROUP BY c.country)
SELECT country,profit,sum(grand_total) as grand_total
       ,sum(percent_total) as percent_total
	   ,sum(fraction) as fraction
	   ,sum(degrees) as degrees
FROM pie_graph_data
GROUP BY GROUPING SETS ((country,profit),())