USE OLAP
--tabular report
SELECT c.country, s.order_method_type,s.revenue
FROM sales_enriched as s JOIN countries as c ON s.retailer_city=c.city

--Create a column for each method type containing the measure.
SELECT distinct s.order_method_type
FROM sales_enriched as s

SELECT c.country, s.order_method_type
	   ,s.revenue as [E-mail]
       ,s.revenue as [Web]
	   ,s.revenue as [Telephone]
	   ,s.revenue as [Mail]
	   ,s.revenue as [Sales visit]
FROM sales_enriched as s JOIN countries as c ON s.retailer_city=c.city

--'Web' column
SELECT c.country, s.order_method_type
	   ,s.revenue as [E-mail]
       ,CASE WHEN s.order_method_type='Web' THEN s.revenue ELSE 0 END as [Web]
	   ,s.revenue as [Telephone]
	   ,s.revenue as [Mail]
	   ,s.revenue as [Sales visit]
FROM sales_enriched as s JOIN countries as c ON s.retailer_city=c.city

--Repeat this case statement for each of the columns 
SELECT c.country, s.order_method_type
	   ,CASE WHEN s.order_method_type='E-mail' THEN s.revenue ELSE 0 END as [E-mail]
       ,CASE WHEN s.order_method_type='Web' THEN s.revenue ELSE 0 END as [Web]
	   ,CASE WHEN s.order_method_type='Telephone' THEN s.revenue ELSE 0 END as [Telephone]
	   ,CASE WHEN s.order_method_type='Mail' THEN s.revenue ELSE 0 END as [Mail]
	   ,CASE WHEN s.order_method_type='Sales visit' THEN s.revenue ELSE 0 END as [Sales visit]
FROM sales_enriched as s JOIN countries as c ON s.retailer_city=c.city

--Remove the s.order_method_type
SELECT c.country
	   ,CASE WHEN s.order_method_type='E-mail' THEN s.revenue ELSE 0 END as [E-mail]
       ,CASE WHEN s.order_method_type='Web' THEN s.revenue ELSE 0 END as [Web]
	   ,CASE WHEN s.order_method_type='Telephone' THEN s.revenue ELSE 0 END as [Telephone]
	   ,CASE WHEN s.order_method_type='Mail' THEN s.revenue ELSE 0 END as [Mail]
	   ,CASE WHEN s.order_method_type='Sales visit' THEN s.revenue ELSE 0 END as [Sales visit]
FROM sales_enriched as s JOIN countries as c ON s.retailer_city=c.city

--Revenues by country and method_type
SELECT c.country
	   ,sum(CASE WHEN s.order_method_type='E-mail' THEN s.revenue ELSE 0 END) as [E-mail]
       ,sum(CASE WHEN s.order_method_type='Web' THEN s.revenue ELSE 0 END) as [Web]
	   ,sum(CASE WHEN s.order_method_type='Telephone' THEN s.revenue ELSE 0 END) as [Telephone]
	   ,sum(CASE WHEN s.order_method_type='Mail' THEN s.revenue ELSE 0 END) as [Mail]
	   ,sum(CASE WHEN s.order_method_type='Sales visit' THEN s.revenue ELSE 0 END) as [Sales visit]
FROM sales_enriched as s JOIN countries as c ON s.retailer_city=c.city
GROUP BY c.country --don't forget the group by for our dimension
