Market Analysis: First Item
ASKED IN INTERVIEWAmazon
Problem Statement
Amazon marketing wants to know if users' early shopping behavior aligns with their stated brand preferences.
Write an SQL query to find, for each user, whether the brand of the second item (by date) they purchased is the same as their favorite brand.
Rules:
- If a user has purchased less than two items, the answer should be "no".
- Output columns: seller_id, 2nd_item_fav_brand.
- 2nd_item_fav_brand should be "yes" if the brands match, and "no" otherwise.
- Results must be sorted by seller_id in ascending order.
Tests your understanding of
Window Functions, Ranking and Joins
Input Tables
users
| user_id(INTEGER) | join_date(DATE) | favorite_brand(VARCHAR) |
|---|---|---|
| 1 | 2025-01-01 | Samsung |
| 2 | 2025-02-09 | Apple |
| 3 | 2025-01-19 | LG |
| 4 | 2025-05-21 | HP |
orders
| order_id(INTEGER) | order_date(DATE) | item_id(INTEGER) | seller_id(INTEGER) |
|---|---|---|---|
| 1 | 2025-08-01 | 101 | 1 |
| 2 | 2025-08-02 | 102 | 1 |
| 3 | 2025-08-03 | 103 | 2 |
| 4 | 2025-08-04 | 104 | 2 |
| 5 | 2025-08-05 | 101 | 3 |
items
| item_id(INTEGER) | item_brand(VARCHAR) |
|---|---|
| 101 | Samsung |
| 102 | Samsung |
| 103 | Apple |
| 104 | Sony |
Expected Output
| seller_id(INTEGER) | 2nd_item_fav_brand(VARCHAR) |
|---|---|
| 1 | yes |
| 2 | no |
| 3 | no |
| 4 | no |
Tags
MediumASKED IN INTERVIEWWindow FunctionsRankingJoins
20-25 min
55%