Find Active Users - Problem

You are given a table Users containing purchase records. Your task is to identify active users.

An active user is defined as a user who has made a second purchase within 7 days of any other of their purchases.

Important: "Within 7 days" means if one purchase is on May 31, 2023, then any purchase between May 31, 2023, and June 7, 2023 (inclusive) qualifies as within 7 days.

Return a list of user_id values representing the active users in any order.

Table Schema

Users
Column Name Type Description
user_id PK int User identifier
item varchar Name of purchased item
created_at PK datetime Date and time of purchase
amount int Purchase amount in cents
Primary Key: user_id, created_at
Note: This table may contain duplicate records. Each row represents a purchase transaction.

Input & Output

Example 1 — Mixed Activity Levels
Input Table:
user_id item created_at amount
1 Book 2023-05-01 1500
1 Phone 2023-05-05 50000
2 Laptop 2023-05-01 80000
2 Mouse 2023-05-15 2500
Output:
user_id
1
💡 Note:

User 1: Made purchases on 2023-05-01 and 2023-05-05. The second purchase is 4 days after the first, which is within 7 days. Therefore, User 1 is active.

User 2: Made purchases on 2023-05-01 and 2023-05-15. The second purchase is 14 days after the first, which exceeds 7 days. Therefore, User 2 is not active.
Example 2 — Multiple Active Users
Input Table:
user_id item created_at amount
3 Tablet 2023-06-01 30000
3 Case 2023-06-07 1000
4 Monitor 2023-06-10 25000
4 Keyboard 2023-06-12 5000
5 Headphones 2023-06-01 8000
Output:
user_id
3
4
💡 Note:

User 3: Purchased on 2023-06-01 and 2023-06-07 (exactly 6 days apart, within 7 days). Active user.

User 4: Purchased on 2023-06-10 and 2023-06-12 (2 days apart, within 7 days). Active user.

User 5: Only made one purchase, so cannot be considered active.
Example 3 — Boundary Case
Input Table:
user_id item created_at amount
6 Watch 2023-07-01 15000
6 Band 2023-07-08 2000
7 Camera 2023-07-01 40000
7 Lens 2023-07-09 20000
Output:
user_id
💡 Note:

User 6: Purchased on 2023-07-01 and 2023-07-08 (7 days apart, but this is 8 days since we count inclusively: July 1→8 is 8 days). Not active.

User 7: Purchased on 2023-07-01 and 2023-07-09 (8 days apart, exceeds 7 days). Not active.

No users qualify as active in this example.

Constraints

  • 1 ≤ user_id ≤ 10000
  • item is a non-empty string
  • created_at is a valid datetime
  • 1 ≤ amount ≤ 100000
  • The table may contain duplicate records

Visualization

Tap to expand
Find Active Users: Purchase Pattern AnalysisInput: Users Purchase Datauser_iditemcreated_at1Book05-011Phone05-052Laptop05-012Mouse05-15SELF JOIN7-day filterOutput: Active Usersuser_id1Analysis: Purchase Time WindowsUser 1: ACTIVE05-01 → 05-054 days ≤ 7 ✓User 2: INACTIVE05-01 → 05-1514 days > 7 ✗
Understanding the Visualization
1
Input
Users table with purchase records
2
Self-Join
Compare purchase dates within user groups
3
Output
Active users with qualifying purchase patterns
Key Takeaway
🎯 Key Insight: Self-joins with date arithmetic efficiently identify temporal patterns in user behavior

Related Problems

Asked in
Amazon 15 Facebook 12 Google 8
28.5K 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