USE OLAP
--Question 1
--a. Create a view that shows the number of sales for particular product lines by month and year. As we will
--   be dealing with fractions in later calculations make sure to convert it to a float before displaying it.
go 
CREATE VIEW number_by_year_and_month as
SELECT p.product_line,t.year,t.month,cast(count(*) as float) as number_of_sales
FROM sales as s JOIN products as p ON s.product_code=p.code
			    JOIN times as t ON s.date=t.date
GROUP BY p.product_line,t.year,t.month
go
SELECT * FROM number_by_year_and_month
ORDER BY product_line,year,month

--b. Add a measure that displays the total number of sales for each product line in that year. 
SELECT product_line,year,month,number_of_sales
       ,sum(number_of_sales) over(partition by product_line,year) as total_number_of_sales_for_year
FROM number_by_year_and_month
ORDER BY product_line,year,month

--c. Add a measure that displays the number of sales as a % of the total (rounded to 2 decimal places)
SELECT product_line,year,month,number_of_sales
       ,sum(number_of_sales) over(partition by product_line,year) as total_number_of_sales_for_year
	   ,round(number_of_sales/sum(number_of_sales) over(partition by product_line,year)*100,2) as percent_of_total
FROM number_by_year_and_month
ORDER BY product_line,year,month

--d. Add another measure to show the sales to date for each month.
SELECT product_line,year,month,number_of_sales
       ,sum(number_of_sales) over(partition by product_line,year) as total_number_of_sales_for_year
	   ,round(number_of_sales/sum(number_of_sales) over(partition by product_line,year)*100,2) as percent_of_total
	   ,sum(number_of_sales) over (partition by product_line,year order by month) as sales_to_date
FROM number_by_year_and_month
ORDER BY product_line,year,month

--e. Write a measure that displays the previous months number_of_sales
SELECT product_line,year,month,number_of_sales
       ,sum(number_of_sales) over(partition by product_line,year) as total_number_of_sales_for_year
	   ,round(number_of_sales/sum(number_of_sales) over(partition by product_line,year)*100,2) as percent_of_total
	   ,sum(number_of_sales) over (partition by product_line,year order by month) as sales_to_date
	   ,sum(number_of_sales) over (order by product_line,year rows between 1 preceding and 1 preceding) 
	        as previous_months_sales
FROM number_by_year_and_month
ORDER BY product_line,year,month

--f. Use the previous calculation to display the increase or decrease in the number of sales 
--   compared to the previous month.
SELECT product_line,year,month,number_of_sales
       ,sum(number_of_sales) over(partition by product_line,year) as total_number_of_sales_for_year
	   ,round(number_of_sales/sum(number_of_sales) over(partition by product_line,year)*100,2) as percent_of_total
	   ,sum(number_of_sales) over (partition by product_line,year order by month) as sales_to_date
	   ,sum(number_of_sales) over (order by product_line,year rows between 1 preceding and 1 preceding) 
	        as previous_months_sales
	   ,number_of_sales
	    -sum(number_of_sales) over (order by product_line,year rows between 1 preceding and 1 preceding)
		as increase
FROM number_by_year_and_month
ORDER BY product_line,year,month

--g. (Challenge) Add a final column that uses a case statement to display a ⚠ if the number sold
--    decreased. (Use nchar(9888) for the symbol)
SELECT product_line,year,month,number_of_sales
       ,sum(number_of_sales) over(partition by product_line,year) as total_number_of_sales_for_year
	   ,round(number_of_sales/sum(number_of_sales) over(partition by product_line,year)*100,2) as percent_of_total
	   ,sum(number_of_sales) over (partition by product_line,year order by month) as sales_to_date
	   ,sum(number_of_sales) over (order by product_line,year rows between 1 preceding and 1 preceding) 
	        as previous_months_sales
	   ,number_of_sales
	    -sum(number_of_sales) over (order by product_line,year rows between 1 preceding and 1 preceding)
		as increase
	   ,CASE WHEN number_of_sales-sum(number_of_sales) 
	              over (order by product_line,year rows between 1 preceding and 1 preceding) < 0
			 THEN nchar(9888) ELSE ''
		END as warning
FROM number_by_year_and_month
ORDER BY product_line,year,month