-
Notifications
You must be signed in to change notification settings - Fork 0
/
Highest-Grossing Items [Amazon SQL Interview Question].sql
56 lines (42 loc) · 1.83 KB
/
Highest-Grossing Items [Amazon SQL Interview Question].sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
-- Assume you're given a table containing data on Amazon customers and their spending on products in different categorie, write a query to identify the top two highest-grossing products within each category in the year 2022. The output should include the category, product, and total spend.
-- product_spend Table:
-- Column Name Type
-- category string
-- product string
-- user_id integer
-- spend decimal
-- transaction_date timestamp
-- product_spend Example Input:
-- category product user_id spend transaction_date
-- appliance refrigerator 165 246.00 12/26/2021 12:00:00
-- appliance refrigerator 123 299.99 03/02/2022 12:00:00
-- appliance washing machine 123 219.80 03/02/2022 12:00:00
-- electronics vacuum 178 152.00 04/05/2022 12:00:00
-- electronics wireless headset 156 249.90 07/08/2022 12:00:00
-- electronics vacuum 145 189.00 07/15/2022 12:00:00
-- Example Output:
-- category product total_spend
-- appliance refrigerator 299.99
-- appliance washing machine 219.80
-- electronics vacuum 341.00
-- electronics wireless headset 249.90
-- Explanation:
-- Within the "appliance" category, the top two highest-grossing products are "refrigerator" and "washing machine."
-- In the "electronics" category, the top two highest-grossing products are "vacuum" and "wireless headset."
-- Solution
WITH ranking_cte AS (
SELECT category
, product
, SUM(spend) AS total_spend
, RANK() OVER(PARTITION BY category ORDER BY SUM(spend) DESC) AS rnk
FROM product_spend
WHERE EXTRACT(year FROM transaction_date) = '2022'
GROUP BY category
, product
) --End
SELECT category
, product
, total_spend
FROM ranking_cte
WHERE rnk <= 2
;