USE OLAP
/* Question 1
a. Write a query that shows the number of sales in each country 
*/
SELECT c.country,count(*) as number_of_sales
FROM countries as c JOIN sales as s ON c.city=s.retailer_city
GROUP BY c.country
/*
b. Include a column indicating how they rank. Justify which function you used
*/
SELECT c.country,count(*) as number_of_sales
       ,rank() over (order by count(*) desc) as ranking
FROM countries as c JOIN sales as s ON c.city=s.retailer_city
GROUP BY c.country
ORDER BY c.country

/*
c. Justify which function you used:
	RANK:because it's based on rows or 
	DENSE_RANK:because it's based on the values
	either is fine:because it won't make a difference
*/

/*
Question 2
a. Rewrite the query from the previous question to categorise each country into 3 groups
depending on the number of sales each country has.
*/
SELECT c.country,count(*) as number_of_sales
       ,NTILE(3) over (order by count(*) desc) as ranking
FROM countries as c JOIN sales as s ON c.city=s.retailer_city
GROUP BY c.country
ORDER BY c.country

/*b. Use this to categorise each country as
'Red', 'Orange' and 'Yellow' depending on the number of sales */
SELECT c.country,count(*) as number_of_sales
       ,CASE NTILE(3) over (order by count(*) desc)
	         WHEN 3 THEN 'Red'
			 WHEN 2 THEN 'Orange'
			 WHEN 1 THEN 'Green'
		END as ranking
FROM countries as c JOIN sales as s ON c.city=s.retailer_city
GROUP BY c.country
ORDER BY c.country