SQLQuery2.sql

--preformance analysis 

/* Analyze the yearly performance of products by comparing their sales 
to both the average sales performance of the product and the previous year's sales */
with year_product_sales as(
select 
year(s.order_date) order_year,
p.product_name,
sum(s.sales_amount) current_sales
from gold.fact_sales s
left join gold.dim_products p
on p.product_key=s.product_key
where s.order_date is not null
group by year(s.order_date),p.product_name
)

select 
order_year,
product_name,
current_sales,
avg(current_sales)over(partition by product_name ) as avg_sales,
current_sales-avg(current_sales)over(partition by product_name) avg_diff,
case when current_sales-avg(current_sales)over(partition by product_name)>0 then 'above the avg'
	 when current_sales-avg(current_sales)over(partition by product_name)<0 then 'below the avg'
	 else 'AVG'
	 end avg_change,
	 lag(current_sales)over(partition by product_name order by order_year) py_sales,
	 current_sales-lag(current_sales)over(partition by product_name order by order_year)diff_py,
	 case when current_sales-lag(current_sales)over(partition by product_name order by order_year)>0 then 'increase'
	 when current_sales-lag(current_sales)over(partition by product_name order by order_year)<0 then 'decrease'
	 else 'NO CHANGE'
	 end PY_change

from year_product_sales
order by  product_name, order_year