Market Analysis II
Shopify
Problem Statement
Shopify wants to know if sellers are successful in selling their favorite brands.
Write an SQL query to find for each seller, whether the brand of the second item (by date) they sold is their favorite brand.
Rules:
- If a seller has sold less than two items, report 'no'.
- Output columns: seller_id, second_item_fav_brand.
- Results must be sorted by seller_id in ascending order.
Tests your understanding of
Window Functions, Joins and Case Statements
Input Tables
Users
| user_id(INTEGER) | join_date(DATE) | favorite_brand(VARCHAR) |
|---|---|---|
| 1 | 2026-01-01 | Apple |
| 2 | 2026-01-02 | Samsung |
| 3 | 2026-01-03 | Sony |
Orders
| order_id(INTEGER) | order_date(DATE) | item_id(INTEGER) | seller_id(INTEGER) |
|---|---|---|---|
| 1 | 2026-02-01 | 10 | 1 |
| 2 | 2026-02-02 | 11 | 1 |
| 3 | 2026-02-01 | 12 | 2 |
Items
| item_id(INTEGER) | item_brand(VARCHAR) |
|---|---|
| 10 | Samsung |
| 11 | Apple |
| 12 | Samsung |
Expected Output
| seller_id(INTEGER) | second_item_fav_brand(VARCHAR) |
|---|---|
| 1 | yes |
| 2 | no |
| 3 | no |
Tags
MediumWindow FunctionsJoinsCase Statements
25-30 min
46%