-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathDataExpert-SQL41.sql
More file actions
79 lines (75 loc) · 2.25 KB
/
DataExpert-SQL41.sql
File metadata and controls
79 lines (75 loc) · 2.25 KB
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
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
-- Question: Check Second Sale Brand Preference Match
-- https://www.dataexpert.io/question/second-item-preferred-brand-match
-- Approach 01: Using CTE and Window Functions
WITH sold_preferred_brand AS (
SELECT
u.user_id,
u.preferred_brand,
i.item_brand,
ROW_NUMBER() OVER (PARTITION BY u.user_id ORDER BY o.order_date) AS rn,
COUNT(o.order_date) OVER (PARTITION BY u.user_id) AS sold_count
FROM
playground.users u
LEFT JOIN playground.orders o ON u.user_id = o.seller_id
LEFT JOIN playground.items i ON o.item_id = i.item_id
)
SELECT
user_id AS seller_id,
CASE
WHEN preferred_brand = item_brand
AND sold_count >= 2 THEN 'yes'
ELSE 'no'
END AS has_pref_brand
FROM
sold_preferred_brand
WHERE
sold_count < 2
UNION ALL
SELECT
user_id,
CASE
WHEN preferred_brand = item_brand
AND sold_count >= 2 THEN 'yes'
ELSE 'no'
END AS has_pref_brand
FROM
sold_preferred_brand
WHERE
rn = 2;
-- Approach 02:
-- Step 1: Create a CTE to rank all sales for each seller by date.
WITH ranked_sales AS (
SELECT
u.user_id,
u.preferred_brand,
i.item_brand,
-- Use a window function to assign a rank to each sale for a given seller
ROW_NUMBER() OVER (PARTITION BY u.user_id ORDER BY o.order_date) AS sale_rank
FROM
playground.users u
JOIN playground.orders o ON u.user_id = o.seller_id
JOIN playground.items i ON o.item_id = i.item_id
),
-- Step 2: Create a second CTE to isolate only the second sale (where rank = 2)
-- and check if the item brand matches the preferred brand.
second_sale_details AS (
SELECT
user_id,
CASE WHEN preferred_brand = item_brand THEN 'yes' ELSE 'no' END AS pref_brand_on_second_sale
FROM
ranked_sales
WHERE
sale_rank = 2 -- Filter on the calculated rank here
)
-- Step 3: Select all users and LEFT JOIN them with the second sale details.
-- This ensures that every user is included, even those with 0 or 1 sale.
SELECT
u.user_id AS seller_id,
-- If a user has a record in the second_sale_details CTE, use that result.
-- Otherwise (for users with < 2 sales), default the value to 'no'.
COALESCE(s.pref_brand_on_second_sale,'no') AS has_pref_brand
FROM
playground.users u
LEFT JOIN second_sale_details s ON u.user_id = s.user_id
ORDER BY
u.user_id;