order_data_analysis_sql.sql

SELECT * from [master].[dbo].[orders]




--find top 10 highest reveue generating products 
select top 10 product_id,sum(sale_price) as sales
from orders
group by product_id
order by sales desc





--find top 5 highest selling products in each region
with cte as (
select product_id,sum(sale_price) as sales,region
from orders
group by product_id,region
)
select * from(
select *,ROW_NUMBER() over(partition by region order by sales desc) as rn
from cte ) cte2
where rn <=5






--find month over month growth comparison for 2022 and 2023 sales eg : jan 2022 vs jan 2023
with cte as(
select year(order_date) as order_year ,MONTH(order_date) as order_month,sum (sale_price) as sales 
from orders
group by year(order_date),MONTH(order_date))

select order_month
,sum(case when order_year=2022 then sales else 0 end) as sales_2022
,sum(case when order_year=2023 then sales else 0 end) as sales_2023
from cte 
group by order_month
order by order_month







--for each category which month had highest sales 
with cte as(
select category,sum(sale_price)as sales,format(order_date,'yyyy-MM') as order_month
from orders
group by category,format(order_date,'yyyy-MM')
) 
select * from (
select * , ROW_NUMBER() over (partition by category order by sales desc ) as rn
from cte
)A
WHERE rn=1







--which sub category had highest growth by profit in 2023 compare to 2022
with cte as (
select sub_category,year(order_date) as order_year,
sum(sale_price) as sales
from orders
group by sub_category,year(order_date)
--order by year(order_date),month(order_date)
	)
, cte2 as (
select sub_category
, sum(case when order_year=2022 then sales else 0 end) as sales_2022
, sum(case when order_year=2023 then sales else 0 end) as sales_2023
from cte 
group by sub_category
)
select top 1 *
,(sales_2023-sales_2022)
from  cte2
order by (sales_2023-sales_2022) desc