WITH quarter_data as ( SELECT *, concat(year(sale_date),'-Q', datepart(quarter,sale_date)) as quarter from prd_sales ), ranked_data AS ( SELECT *, dense_rank() over(partition by quarter order by sale_qty DESC) AS drn ,sum(sale_qty) over(partition by quarter, product_id) AS total_quarter_sale FROM quarter_data ) SELECT product_id, product_name, total_quarter_sale, quarter FROM ranked_data WHERE drn = 1
select * from ( select product_id,product_name,sale_qty,sale_date,row_number() over(partition by substr(sale_date,7,4 ) order by sale_qty desc) as rank from product_sales where substr(sale_date,4,2) < 4 ) where rank = 1 union select * from ( select product_id,product_name,sale_qty,sale_date,row_number() over(partition by substr(sale_date,7,4 ) order by sale_qty desc) as rank from product_sales where substr(sale_date,4,2) between 4 and 6 ) where rank = 1 union select * from ( select product_id,product_name,sale_qty,sale_date,row_number() over(partition by substr(sale_date,7,4 ) order by sale_qty desc) as rank from product_sales where substr(sale_date,4,2) between 7 and 9 ) where rank = 1 union select * from ( select product_id,product_name,sale_qty,sale_date,row_number() over(partition by substr(sale_date,7,4 ) order by sale_qty desc) as rank from product_sales where substr(sale_date,4,2) between 9 and 12 ) where rank = 1 ;
Hi Folks I have a different approach : WITH cte AS (select *, case when sale_Date between '2021-01-01' and '2021-03-01' then 'Q1-2021' when sale_Date between '2021-04-01' and '2021-06-01' then 'Q2-2021' when sale_Date between '2021-07-01' and '2021-09-01' then 'Q3-2021' when sale_Date between '2021-10-01' and '2021-12-01' then 'Q4-2021' when sale_Date between '2022-01-01' and '2022-03-01' then 'Q1-2022' end as "Quater" from prd_sales ),cte1 as (select *,sum(sale_qty) over (partition by Quater,Product_id ) Total from cte),cte2 as ( select Product_id,Product_name,Quater,sale_Date,max(Total) over (partition by Quater,Product_id) Top1 from cte1),cte3 as (select distinct Product_id,Product_name,Quater,Top1,DENSE_RANK() over (partition by Quater order by Top1 desc) rn from cte2) select * from cte3 where rn = 1 order by Quater
select product_id ,product_name, sal_qty,quarter from (select product_id ,product_name,total_sale,max(total_sale) over(partition by quarter) as 'sal_qty',quarter from (select product_id,product_name,sum(sale_qty) as total_sale, case when sale_date >= '2021-01-01' and sale_date = '2021-04-01' and sale_date = '2021-07-01' and sale_date = '2021-10-01' and sale_date
WITH cte AS ( SELECT product_id, product_name, sale_date, sale_qty, EXTRACT(YEAR FROM sale_date) AS year, CEIL(EXTRACT(MONTH FROM sale_date) / 3) AS quarter, SUM(sale_qty) AS total_sales_qty GROUP BY product_id, product_name, year, quarter ) cte1 AS ( SELECT product_id, product_name, sale_date, sale_qty, year, quantity, ROW_NUMBER() OVER (PARTITION BY year, quarter ORDER BY total_sales_qty DESC) AS rnk FROM cte ) SELECT Product_id, Product_name, year, quarter, total_sales_qty FROM cte WHERE rnk = 1 ORDER BY year, quarter;
really helpful✌🤟
Great real time example and very nice explanations.. please keep updating this kinda real time scenarios
Sure, Thank you very much for your feedback
Excellent justification, sir. We require more video of real Secanario-type issues so that we may comprehend them better.
Thank You
Then how to get only last quarter Total sales here
WITH quarter_data as (
SELECT *, concat(year(sale_date),'-Q', datepart(quarter,sale_date)) as quarter
from prd_sales
), ranked_data AS (
SELECT *,
dense_rank() over(partition by quarter order by sale_qty DESC) AS drn
,sum(sale_qty) over(partition by quarter, product_id) AS total_quarter_sale
FROM quarter_data
)
SELECT product_id, product_name, total_quarter_sale, quarter
FROM ranked_data
WHERE drn = 1
We can use union and substr function to get the max sale qty on each quarter.
select * from
(
select product_id,product_name,sale_qty,sale_date,row_number() over(partition by substr(sale_date,7,4 ) order by sale_qty desc) as rank
from product_sales where substr(sale_date,4,2) < 4
) where rank = 1
union
select * from
(
select product_id,product_name,sale_qty,sale_date,row_number() over(partition by substr(sale_date,7,4 ) order by sale_qty desc) as rank
from product_sales where substr(sale_date,4,2) between 4 and 6
) where rank = 1
union
select * from
(
select product_id,product_name,sale_qty,sale_date,row_number() over(partition by substr(sale_date,7,4 ) order by sale_qty desc) as rank
from product_sales where substr(sale_date,4,2) between 7 and 9
) where rank = 1
union
select * from
(
select product_id,product_name,sale_qty,sale_date,row_number() over(partition by substr(sale_date,7,4 ) order by sale_qty desc) as rank
from product_sales where substr(sale_date,4,2) between 9 and 12
) where rank = 1 ;
Thank you for sharing
Hi Folks I have a different approach :
WITH cte AS (select *,
case when sale_Date between '2021-01-01' and '2021-03-01' then 'Q1-2021'
when sale_Date between '2021-04-01' and '2021-06-01' then 'Q2-2021'
when sale_Date between '2021-07-01' and '2021-09-01' then 'Q3-2021'
when sale_Date between '2021-10-01' and '2021-12-01' then 'Q4-2021'
when sale_Date between '2022-01-01' and '2022-03-01' then 'Q1-2022'
end as "Quater" from prd_sales ),cte1 as
(select *,sum(sale_qty) over (partition by Quater,Product_id ) Total from cte),cte2 as (
select Product_id,Product_name,Quater,sale_Date,max(Total) over (partition by Quater,Product_id) Top1 from cte1),cte3 as
(select distinct Product_id,Product_name,Quater,Top1,DENSE_RANK() over (partition by Quater order by Top1 desc) rn from cte2)
select * from cte3
where rn = 1
order by Quater
select product_id ,product_name, sal_qty,quarter from
(select product_id ,product_name,total_sale,max(total_sale) over(partition by quarter) as 'sal_qty',quarter from
(select product_id,product_name,sum(sale_qty) as total_sale,
case
when sale_date >= '2021-01-01' and sale_date = '2021-04-01' and sale_date = '2021-07-01' and sale_date = '2021-10-01' and sale_date
WITH cte AS (
SELECT product_id,
product_name,
sale_date,
sale_qty,
EXTRACT(YEAR FROM sale_date) AS year,
CEIL(EXTRACT(MONTH FROM sale_date) / 3) AS quarter,
SUM(sale_qty) AS total_sales_qty
GROUP BY product_id, product_name, year, quarter
)
cte1 AS (
SELECT product_id,
product_name,
sale_date,
sale_qty,
year,
quantity,
ROW_NUMBER() OVER (PARTITION BY year, quarter ORDER BY total_sales_qty DESC) AS rnk
FROM cte
)
SELECT
Product_id,
Product_name,
year,
quarter,
total_sales_qty
FROM
cte
WHERE rnk = 1
ORDER BY year, quarter;