--Question 1.
--Create a view for total revenue by year and month. We will use this view in the next
--chapter.
go
CREATE VIEW sales_by_year_and_month AS
SELECT t.year,t.month,sum(s.sale_price*quantity_sold) as total_sales
FROM sales as s JOIN times as t ON s.date=t.date
GROUP BY t.year,t.month
go

SELECT * FROM sales_by_year_and_month
ORDER BY year,month

--Qestion 2. 
--a. Create a view called sales_enriched that includes the following additional 
--  Columns and calculations:
--		Revenue (total of sales_price*quantity)
--		Product cost (hint: use a join to the product table)
--      Total cost (cost*quantity)
--		Profit (total of sales price - product cost)
--		Total Profit (profit*quantity)
go
CREATE VIEW sales_enriched AS 
SELECT s.*
       ,s.sale_price*s.quantity_sold as Revenue
	   ,p.product_cost as Product_cost
	   ,p.product_cost*s.quantity_sold as total_cost
	   ,s.sale_price-p.product_cost as Profit
	   ,(s.sale_price-p.product_cost)*s.quantity_sold as total_profit
FROM sales as s JOIN products as p ON s.product_code=p.code
go

--b. Test the view to make sure it works. We will be using it extensively in the next few chapters
SELECT * FROM sales_enriched

--c. Use this enriched_sales to to create a view called presentation_data that includes
--the measures total profit and revenue 
--and the dimensions year, product_type,order method type, country and city
go
CREATE VIEW presentation_data AS
SELECT t.year,p.product_type,s.order_method_type,c.country,c.city
       ,sum(total_profit) as total_profit,sum(revenue) as total_revenue
FROM sales_enriched as s JOIN products as p ON s.product_code = p.code
                         JOIN countries as c ON s.retailer_city=c.city
						 JOIN times as t ON s.date=t.date
GROUP BY t.year,p.product_type,s.order_method_type,c.country,c.city
go

--d. Test the view to make sure it works.
SELECT * FROM presentation_data