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

In this video, we will solve an advanced SQL problem asked in Amazon Interviews.

Given the details of the Amazon customer, specifically focusing on the 'product_spend' table, which contains information about customer purchases, the products they bought, and how much they spent.

You need to find the top two highest-selling products within each category based on total spending. This problem will require us to utilize powerful SQL window functions, which are crucial for solving complex analytical queries efficiently.

Script -
CREATE TABLE ProductSpend (
category VARCHAR(50),
product VARCHAR(100),
user_id INT,
spend DECIMAL(10, 2)
);

INSERT INTO ProductSpend (category, product, user_id, spend) VALUES
('appliance', 'refrigerator', 165, 26.00),
('appliance', 'refrigerator', 123, 3.00),
('appliance', 'washing machine', 123, 19.80),
('electronics', 'vacuum', 178, 5.00),
('electronics', 'wireless headset', 156, 7.00),
('electronics', 'vacuum', 145, 15.00),
('electronics', 'laptop', 114, 999.99),
('fashion', 'dress', 117, 49.99),
('groceries', 'milk', 243, 2.99),
('groceries', 'bread', 645, 1.99),
('home', 'furniture', 276, 599.99),
('home', 'decor', 456, 29.99);

TIMESTAMP -
00:00 Problem Description & Approach
04:45 Solution Implementation on MySQL Workbench

#sql #advancedsql #sqltutorial #sqltutorialforbeginners #sqltutorials #sqltips #coding #dataanalysts #dataanalysis #sqlfordatascience #interviewpreparation #sqlserver #sqlfordataengineer #sqlinterviewquestions #interviewquestionsforfreshers #learnsql #datascienceforbeginners #dataanalysis #microsoft #dataanalysis #amazon #sqlforbeginners

[hairavyadav]

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 <=2;

[jyothishwarriar]

with ranking as
(Select category, product, sum(spend) as total_spend from ProductSpend
group by category, product),
ra as
(select category, product, total_spend, row_number() over(partition by category order by total_spend desc rows between unbounded preceding and unbounded following) as rank_pro from ranking)
select category, product, total_spend from ra
where rank_pro <=2;

[mathman]

with cte as (
select * , ROW_NUMBER() over(partition by category order by sum_spend desc ) rnk from
(select category, product, sum(spend) sum_spend from ProductSpend group by category, product ) as a )

select category, product, sum_spend as total_spend from cte where rnk <=2

[tilu]

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;

[badrilalnagar]

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

[kabalik]

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)

[HARSHRAJ-wzrp]

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<=2;

[avinashjadon]

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 <=2;

[musicallywandering]

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<3;

[yousfimohamed]

with cte as ( SELECT
category,
product,
SUM(spend) AS total_spend,
RANK() OVER (PARTITION BY category ORDER BY SUM(spend) DESC) AS rn
FROM
productspend
GROUP BY
category,
product )
select category, product, total_spend from cte where rn <= 2

[theinsightminer]

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 <=2;

[harshitsalecha]

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<=2;

[saikatmazumder]

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<=2 order by 1, 3 desc;