Repeat Purchase
ASKED IN INTERVIEWe Bay
Problem Statement
Find users who purchased the same item on two or more different days.
Rules:
- Output columns: user_id, item_id, repeat_count.
- repeat_count is the number of unique days the user bought the item.
- Ignore multiple purchases of the same item on the same day.
- Results must be sorted by repeat_count in descending order, then by user_id in ascending order.
Tests your understanding of
Filtering, Aggregation and Date Handling
Input Tables
users
| user_id(INTEGER) | username(VARCHAR) |
|---|---|
| 1 | shopper_alt |
| 2 | bid_master |
| 3 | collect_all |
| 4 | daily_buyer |
| 5 | guest_user |
items
| item_id(INTEGER) | item_name(VARCHAR) |
|---|---|
| 501 | AA Batteries |
| 502 | USB-C Cable |
| 503 | Vintage Watch |
purchases
| purchase_id(INTEGER) | user_id(INTEGER) | item_id(INTEGER) | purchase_date(DATE) |
|---|---|---|---|
| 1 | 1 | 501 | 2024-01-01 |
| 2 | 1 | 501 | 2024-02-01 |
| 3 | 2 | 501 | 2024-01-05 |
| 4 | 2 | 501 | 2024-01-05 |
| 5 | 3 | 502 | 2024-01-10 |
| 6 | 3 | 502 | 2024-01-15 |
| 7 | 3 | 502 | 2024-01-20 |
| 8 | 4 | 503 | 2024-01-01 |
Expected Output
| user_id(INTEGER) | item_id(INTEGER) | repeat_count(INTEGER) |
|---|---|---|
| 3 | 502 | 3 |
| 1 | 501 | 2 |
Tags
MediumASKED IN INTERVIEWFilteringAggregationDate Handling
15-20 min
50%