Amazon SQL Interview Question | SQL Advanced | Window Functions in SQL

[@pratikjugantmohapatra18]

Amazing

[@Gaber22-g6l]

fantastic

[@hairavyadav6579]

with cte as (select *,sum(spend) over(partition by category,product) total from productspend),
cte2 as (select *, dense_rank() over(partition by category order by total desc) rnk from cte)
select distinct category,product,total from cte2 where rnk

[@tilu391]

with cte as
(select category,product,spend, rank() over(partition by category order by spend desc) as rnk from Productspend)
select category,product,spend from cte where rnk=1 or rnk=2;

[@kabalik2699]

select category,product,d from (
select *,rank() over(partition by category order by d desc) f from(
select category,
product,
sum(spend) as d from productspend
group by category,product
order by category,sum(spend) desc)temp)temp1
where f in (1,2)

[@avinashjadon4989]

with cte1 as (
select *,rank() over (partition by category,product order by spend desc) as rn from ProductSpend)
,final as(
select category,product,spend as total_spend,rank() over (partition by category order by spend desc ) as plist from cte1 where rn =1
) select category,product,total_spend from final where plist

[@badrilalnagar9232]

य एवं वेत्ति हन्तारं यश्चैनं मन्यते हतम् ।
उभौ तौ न विजानीतो नायं हन्ति न हन्यते ।। जो इस आत्मा को मारने वाला समझता है तथा जो इसको मरा मानता है , वे दोनों ही नहीं जानते; क्यों कि यह आत्मा वास्तव में न तो किसी को मारता है और न किसी के द्वारा मारा जाता है।।
गीता 2/19.

[@HARSHRAJ-wz2rp]

with cte as(
select category,product,SUM(spend) as x1 FROM ProductSpend GROUP BY category,product
),cte1 as(
select category,product,x1,DENSE_RANK()OVER(PARTITION BY category ORDER BY x1 DESC) as x2 FROM cte
)
select category,product,x1 as total_spend FROM cte1 where x2

[@musicallywandering4617]

WITH CTE AS (
SELECT CATEGORY, PRODUCT,
SUM(SPEND) AS TOTAL_SALES,
DENSE_RANK () OVER (PARTITION BY CATEGORY ORDER BY SUM(SPEND) DESC) AS RK
FROM PRODUCTSPEND
GROUP BY 1, 2
)
SELECT CATEGORY, PRODUCT, TOTAL_SALES
FROM CTE
WHERE RK

[@harshitsalecha221]

SELECT category,
product,
total_spend FROM (SELECT category,
product,
sum(spend) as total_spend,
ROW_NUMBER() OVER(PARTITION BY category ORDER BY sum(spend) DESC) as rn
FROM productspend
GROUP BY category,product) as a
WHERE rn

[@saikatmazumder206]

select category,product,total_amt_spend from (with cte_prod as (select *,sum(spend) as total_amt_spend from productspend group by category,product order by 1,2)
select *,rank() over (partition by category order by product) as rnk from cte_prod)t where rnk

[@theinsightminer08]

SELECT
category,
product,
total_spend
FROM
(SELECT
category,
product,
SUM(spend) AS total_spend,
DENSE_RANK() OVER(PARTITION BY category ORDER BY SUM(spend) DESC) AS rnk
FROM ProductSpend
GROUP BY category,product) AS ranking
WHERE rnk