USE OLAP
--Question 1
--a. create a query to fetch profit by product_type and year.
SELECT p.product_type,t.year,s.profit
FROM sales_enriched as s JOIN times as t ON s.date=t.date
                         JOIN products as p ON s.product_code=p.code
--b. Turn this query into a matrix report using PIVOT. Product_type should be listed
--down the page and year should be listed across the page. 
--hint: use the example in the book as a template for your query
SELECT product_type --column for our country dimension
	   ,[2016],[2017] --headings for our order method types
FROM (SELECT p.product_type,t.year,s.profit
      FROM sales_enriched as s JOIN times as t ON s.date=t.date
                               JOIN products as p ON s.product_code=p.code) as original_query
PIVOT (sum(profit)--sum the profit
	   FOR year IN ([2016],[2017])
	   --for every order method type listed above
	   --this should correspond to the headings we defined in the select statement above.
	   ) as pivot_table

--c.	If you haven’t already done so, make sure the product type is in alphabetical order.
SELECT product_type --column for our country dimension
	   ,[2016],[2017] --headings for our order method types
FROM (SELECT p.product_type,t.year,s.profit
      FROM sales_enriched as s JOIN times as t ON s.date=t.date
                               JOIN products as p ON s.product_code=p.code) as original_query
PIVOT (sum(profit)--sum the profit
	   FOR year IN ([2016],[2017])
	   --for every order method type listed above
	   --this should correspond to the headings we defined in the select statement above.
	   ) as pivot_table
ORDER BY product_type

--Question 2 (Optional)
--Using the matrix report from the previous question try the following challenges
--a. Rewrite the query using a CASE statement instead.
SELECT p.product_type
       ,sum(CASE WHEN t.year = 2016 THEN s.profit END) as [2016]
	   ,sum(CASE WHEN t.year = 2017 THEN s.profit END) as [2017]
FROM sales_enriched as s JOIN times as t ON s.date=t.date
                         JOIN products as p ON s.product_code=p.code
GROUP BY p.product_type
ORDER BY p.product_type

--b. Modify the original query to include totals down the page and at the bottom as well as a grand total.
--   Make sure this query is appropriately formatted.
SELECT p.product_type
       ,format(sum(CASE WHEN t.year = 2016 THEN s.profit END),'c') as [2016]
	   ,format(sum(CASE WHEN t.year = 2017 THEN s.profit END),'c') as [2017]
	   ,format(sum(CASE WHEN t.year = 2016 THEN s.profit END)
	    + sum(CASE WHEN t.year = 2017 THEN s.profit END),'c') as [Total Product Type]
FROM sales_enriched as s JOIN times as t ON s.date=t.date
                         JOIN products as p ON s.product_code=p.code
GROUP BY p.product_type
UNION ALL
SELECT 'Yearly Total'
	   ,format(sum(CASE WHEN t.year = 2016 THEN s.profit END),'c')
	   ,format(sum(CASE WHEN t.year = 2017 THEN s.profit END),'c')
	   ,format(sum(s.profit),'c')
FROM sales_enriched as s JOIN times as t ON s.date=t.date
                         JOIN products as p ON s.product_code=p.code
ORDER BY p.product_type