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 purchase
  • user_id: ID of the user making the purchase
  • purchase_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_date is a valid date

Visualization

Tap to expand
Users With Two Purchases Within 7 Days INPUT: Purchases Table id user date 1 101 2024-01-01 2 101 2024-01-05 3 102 2024-01-01 4 102 2024-01-15 5 103 2024-01-10 6 103 2024-01-12 User 101 Timeline: Jan 1 Jan 5 4 days apart User 102 Timeline: Jan 1 Jan 15 14 days apart ALGORITHM STEPS 1 Self-Join Table Join Purchases with itself on same user_id 2 Calculate Date Diff DATEDIFF(p2.date, p1.date) Between purchase pairs 3 Filter Condition WHERE diff BETWEEN 1 AND 7 Exclude same purchase 4 Select Distinct Users DISTINCT user_id ORDER BY user_id SELECT DISTINCT p1.user_id FROM Purchases p1 JOIN Purchases p2 ON p1.user_id = p2.user_id WHERE DATEDIFF(...) <= 7 ORDER BY user_id FINAL RESULT user_id 101 103 User 101 Jan 1 to Jan 5 = 4 days [OK] User 102 Jan 1 to Jan 15 = 14 days [NO] User 103 Jan 10 to Jan 12 = 2 days [OK] Key Insight: Self-join allows comparing each purchase with every other purchase by the same user. DATEDIFF calculates the gap between dates. Using BETWEEN 1 AND 7 ensures we find pairs within 7 days while excluding comparison of a purchase with itself (diff = 0). DISTINCT removes duplicates. TutorialsPoint - Users With Two Purchases Within Seven Days | Optimal Solution

Related Problems

Asked in
Amazon 23 Microsoft 18
28.0K Views
Medium Frequency
~12 min Avg. Time
890 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen