#sql

[@sandycandy448]

really helpful✌🤟

[@VickyKumar-qp2di]

Excellent justification, sir. We require more video of real Secanario-type issues so that we may comprehend them better.

[@dailylifeofadataengineer7742]

Thank You

[@sanjeetsingh1406]

Great real time example and very nice explanations.. please keep updating this kinda real time scenarios

[@dailylifeofadataengineer7742]

Sure, Thank you very much for your feedback

[@reachrishav]

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

[@mathanrajrajasekaran8754]

We can use union and substr function to get the max sale qty on each quarter.

[@mathanrajrajasekaran8754]

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 ;

[@dailylifeofadataengineer7742]

Thank you for sharing

[@anjibabumakkena]

Then how to get only last quarter Total sales here

[@GowthamR-ro2pt]

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

[@sabesanj5509]

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;