Users With Two Purchases Within Seven Days - Problem
Given a table Purchases containing purchase logs, write a SQL query to find all users who made any two purchases at most 7 days apart.
The Purchases table has the following structure:
purchase_id: Unique identifier for each purchaseuser_id: ID of the user making the purchasepurchase_date: Date when the purchase was made
Return the result table ordered by user_id.
Table Schema
Purchases
| Column Name | Type | Description |
|---|---|---|
purchase_id
PK
|
int | Unique identifier for each purchase |
user_id
|
int | ID of the user making the purchase |
user_id
|
int | ID of the user making the purchase |
purchase_date
|
date | Date when the purchase was made |
Primary Key: purchase_id
Note: Contains logs of purchase dates from a retailer
Input & Output
Example 1 — Users with qualifying purchase pairs
Input Table:
| purchase_id | user_id | purchase_date |
|---|---|---|
| 1 | 1 | 2023-01-01 |
| 2 | 1 | 2023-01-05 |
| 3 | 2 | 2023-01-15 |
| 4 | 3 | 2023-01-10 |
| 5 | 3 | 2023-01-20 |
Output:
| user_id |
|---|
| 1 |
💡 Note:
User 1 made purchases on 2023-01-01 and 2023-01-05, which are 4 days apart (≤ 7 days). User 2 has only one purchase. User 3 made purchases 10 days apart (> 7 days), so doesn't qualify.
Example 2 — Multiple qualifying users
Input Table:
| purchase_id | user_id | purchase_date |
|---|---|---|
| 1 | 1 | 2023-01-01 |
| 2 | 1 | 2023-01-07 |
| 3 | 2 | 2023-01-10 |
| 4 | 2 | 2023-01-12 |
| 5 | 3 | 2023-01-20 |
Output:
| user_id |
|---|
| 1 |
| 2 |
💡 Note:
User 1 has purchases exactly 6 days apart (Jan 1 & Jan 7), User 2 has purchases 2 days apart (Jan 10 & Jan 12). User 3 has only one purchase.
Example 3 — No qualifying users
Input Table:
| purchase_id | user_id | purchase_date |
|---|---|---|
| 1 | 1 | 2023-01-01 |
| 2 | 1 | 2023-01-10 |
| 3 | 2 | 2023-01-15 |
Output:
| user_id |
|---|
💡 Note:
User 1's purchases are 9 days apart (> 7 days), and User 2 has only one purchase. No users qualify.
Constraints
-
1 ≤ purchase_id ≤ 1000 -
1 ≤ user_id ≤ 1000 -
purchase_dateis a valid date
Visualization
Tap to expand
Understanding the Visualization
1
Input
Purchases table with user purchases and dates
2
Self-Join
Compare different purchases by same user
3
Output
Users with purchases ≤ 7 days apart
Key Takeaway
🎯 Key Insight: Self-join technique efficiently finds related records within the same table by comparing different rows with matching criteria
💡
Explanation
AI Ready
💡 Suggestion
Tab
to accept
Esc
to dismiss
// Output will appear here after running code