Find Active Users

Find Active Users - Problem

Database Medium

You're working for an e-commerce platform that needs to identify active customers for targeted marketing campaigns. An active user is someone who has made multiple purchases within a short time window - specifically, a user who has made a second purchase within 7 days of any other purchase.

Given: A database table Users containing purchase records:

Column Name	Type
user_id	int
item	varchar
created_at	datetime
amount	int

Goal: Return a list of user_ids who qualify as active users.

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

Example: If a user bought an item on June 1st and another item on June 6th, they're active (5 days apart). If they bought on June 1st and June 8th, they're still active (7 days apart, inclusive).

Input & Output

Basic Example - Mixed Active and Inactive Users

$ Input: Users = [[1, 'laptop', '2023-05-01', 1000], [1, 'mouse', '2023-05-03', 50], [2, 'book', '2023-06-01', 20], [2, 'pen', '2023-06-15', 5], [3, 'phone', '2023-07-01', 800], [3, 'case', '2023-07-08', 25]]

› Output: [1, 3]

💡 Note: User 1: purchased on May 1 and May 3 (2 days apart ≤ 7) → Active. User 2: purchased on Jun 1 and Jun 15 (14 days apart > 7) → Not active. User 3: purchased on Jul 1 and Jul 8 (7 days apart ≤ 7) → Active.

Edge Case - Exact 7-Day Boundary

$ Input: Users = [[1, 'item1', '2023-05-01', 100], [1, 'item2', '2023-05-08', 200], [2, 'item3', '2023-06-01', 150], [2, 'item4', '2023-06-09', 75]]

› Output: [1]

💡 Note: User 1: May 1 to May 8 is exactly 7 days (inclusive) → Active. User 2: Jun 1 to Jun 9 is 8 days > 7 → Not active. This tests the inclusive boundary condition.

Single Purchase Users

$ Input: Users = [[1, 'book', '2023-05-01', 25], [2, 'pen', '2023-06-01', 10], [3, 'notebook', '2023-07-01', 15]]

› Output: []

💡 Note: All users have only one purchase each, so none can be considered active (need at least 2 purchases within 7 days). Returns empty list.

Constraints

1 ≤ number of records ≤ 10⁴
1 ≤ user_id ≤ 10³
created_at is in 'YYYY-MM-DD' format
1 ≤ amount ≤ 10⁴
item is a non-empty string with length ≤ 50

Visualization

Tap to expand

Understanding the Visualization

Collect Visit Data

Gather all customer visit records with timestamps

Organize by Customer

Group visits by customer ID and sort chronologically

Check Return Visits

For each customer, see if any two visits are within 7 days

Mark Loyal Customers

Customers with quick return visits get loyalty status

Key Takeaway

🎯 Key Insight: By sorting dates first, we only need to check consecutive visits rather than all possible pairs, making the solution much more efficient!

Asked in

a Amazon 45 G Google 38 f Meta 32 ⊞ Microsoft 28

The optimal approach is the two-pass hash table method. First, group purchases by user and sort dates chronologically. Then, for each user, check only consecutive date pairs for the 7-day window. This reduces time complexity from O(n²) to O(n log n) by avoiding unnecessary comparisons of distant dates.

Common Approaches

Approach	Time	Space	Notes
✓ Brute Force (Compare All Pairs)	O(n²)	O(n)	For each user, compare every purchase with every other purchase to find pairs within 7 days
Two-Pass Hash Table	O(n log n)	O(n)	First pass: group purchases by user and sort dates. Second pass: check consecutive dates within 7 days

Brute Force (Compare All Pairs) — Algorithm Steps

Group all purchase records by user_id
For each user, get all their purchase dates
Compare every purchase date with every other purchase date for that user
If any pair of dates is within 7 days, mark user as active
Return list of all active user_ids

Visualization

Tap to expand

Step-by-Step Walkthrough

Group by User

Organize purchases by user_id

Compare All Pairs

For each user, compare every purchase date with every other

Check 7-Day Window

Mark user as active if any pair is within 7 days

Code -

solution.c — C

Time & Space Complexity

Time Complexity

⏱️

O(n²)

In worst case, if all purchases belong to one user, we compare each purchase with every other purchase

⚠ Quadratic Growth

Space Complexity

O(n)

Space to store purchases grouped by user and result set

⚡ Linearithmic Space

47.3K Views

Medium Frequency

~18 min Avg. Time

1.5K 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