SQLQuery1.sql

--the sales by year
select year(order_date) as order_year,
sum(sales_amount) as total_sales
from gold.fact_sales
where year(order_date) is not  null
group by year(order_date)
order by year(order_date)

-- how many new customers were added evrey year
select 
DATETRUNC(YEAR,order_date)as  order_date,
count(distinct customer_key) total_customers
from gold.fact_sales
group by DATETRUNC(year,order_date)
order by DATETRUNC(year,order_date)

--calculate the total sales per month 
-- and the running of the sales over time 
select
order_date,
total_sales,
sum(total_sales)over(order by order_date) as running_total_sales,
avg(AVG_price)over(order by order_date) as moving_average_price
from
	(select 
	datetrunc(MONTH, order_date) as order_date,
	sum(sales_amount) as total_sales,
	avg(price) as AVG_price
	from gold.fact_sales
	where datetrunc(MONTH, order_date) is not null
	group by datetrunc(MONTH, order_date)
	)t
	

	/* 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



-- which categories contribute the most to overall sales
with category_sales as(
select 
category  ,
sum(sales_amount) total_sales
from gold.fact_sales s
left join gold.dim_products p
on p.product_key=s.product_key
group by category)

select 
category,
total_sales ,
sum(total_sales) over() as sales_overall,
concat(round((cast(total_sales as float)/sum(total_sales) over())*100,2),'%') as percentage_of_total
from category_sales
order by total_sales desc


with customer_spending as(
select 
c.customer_key,
sum(s.sales_amount) as total_spending,
min(order_date) first_order,
max(order_date) last_order,
datediff(month,min(order_date),max(order_date)) as life_spend
from gold.fact_sales s
left join gold.dim_customers c
on c.customer_key= s.customer_key
group by c.customer_key 
)

select
count(customer_key) total_customer,
customer_segment
from (
	select 
customer_key,
CASE WHEN life_spend >= 12 AND total_spending > 5000 THEN 'VIP'
     WHEN life_spend >= 12 AND total_spending <= 5000 THEN 'Regular'
     ELSE 'New'
        END AS customer_segment
from customer_spending
)t
group by customer_segment
order by total_customer desc