Active Users - Practice Coding Problems

Active Users - Problem

Database Medium

Active Users Analytics

You're building a user engagement analytics system for a social media platform. Your task is to identify active users - those who demonstrate consistent engagement by logging in for 5 or more consecutive days.

Given two database tables:

Accounts Table: Contains user account information (id and name)
Logins Table: Contains login records (account id and login date)

Your goal is to write an SQL query that finds all users who have logged in for at least 5 consecutive days. Return their id and name, ordered by id.

Note: A user may log in multiple times per day, but we only care about the distinct login dates for consecutive day calculations.

Input & Output

example_1.sql — Basic consecutive logins

$ Input: Accounts: [(1,'Winston'), (7,'Jonathan')] Logins: [(7,'2020-05-30'), (1,'2020-05-30'), (7,'2020-05-31'), (7,'2020-06-01'), (7,'2020-06-02'), (7,'2020-06-03'), (7,'2020-06-04'), (1,'2020-06-01')]

› Output: [(7,'Jonathan')]

💡 Note: User 7 (Jonathan) logged in for 5 consecutive days from 2020-05-30 to 2020-06-03, making him an active user. User 1 (Winston) only has 2 separate login days, not meeting the 5 consecutive days requirement.

example_2.sql — Multiple consecutive streaks

$ Input: Accounts: [(1,'Alice'), (2,'Bob'), (3,'Charlie')] Logins: [(1,'2023-01-01'), (1,'2023-01-02'), (1,'2023-01-03'), (1,'2023-01-04'), (1,'2023-01-05'), (2,'2023-01-01'), (2,'2023-01-03'), (2,'2023-01-05'), (3,'2023-01-10'), (3,'2023-01-11'), (3,'2023-01-12'), (3,'2023-01-13'), (3,'2023-01-14'), (3,'2023-01-15')]

› Output: [(1,'Alice'), (3,'Charlie')]

💡 Note: Alice has 5 consecutive days (Jan 1-5), Charlie has 6 consecutive days (Jan 10-15). Bob has non-consecutive login days, so he doesn't qualify as an active user.

example_3.sql — Edge case with duplicates

$ Input: Accounts: [(1,'John')] Logins: [(1,'2023-01-01'), (1,'2023-01-01'), (1,'2023-01-02'), (1,'2023-01-03'), (1,'2023-01-04'), (1,'2023-01-05'), (1,'2023-01-05')]

› Output: [(1,'John')]

💡 Note: Even with duplicate login entries for the same day, John still has 5 consecutive unique login days (Jan 1-5), making him an active user. Duplicates are handled by using DISTINCT in the query.

Visualization

Tap to expand

5 consecutive daysGroup B: 2 consecutive days✅ User 1 is ACTIVE (Group A ≥ 5 days)

Understanding the Visualization

Remove Duplicates

Get unique login dates per user (same person can't visit gym twice in one day)

Rank Dates

Apply DENSE_RANK to order login dates chronologically for each user

Group Consecutive

Calculate date groups by subtracting rank from date - consecutive dates get same group

Count & Filter

Count group sizes and keep users with 5+ consecutive login days

Key Takeaway

🎯 Key Insight: DENSE_RANK creates consistent group identifiers for consecutive date sequences, allowing efficient detection of active users in O(n log n) time

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

Single sort for DENSE_RANK operation, then linear grouping and filtering

⚡ Linearithmic

Space Complexity

O(n)

Space for intermediate results and window function processing

⚡ Linearithmic Space

Constraints

1 ≤ Accounts table size ≤ 500
1 ≤ Logins table size ≤ 1000
1 ≤ id ≤ 500
All login_date values are valid dates
A user may have multiple login records for the same date

Asked in

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

The optimal solution uses DENSE_RANK() window function to efficiently detect consecutive login patterns. The key insight is that consecutive dates have the same date_group when we subtract the rank from the login date. This allows us to group consecutive sequences and count their lengths in O(n log n) time.

Common Approaches

Approach	Time	Space	Notes
✓ Brute Force (Self-Join Approach)	O(n⁵)	O(n)	Use self-joins to check every possible 5-day consecutive sequence
DENSE_RANK Window Function (Optimal)	O(n log n)	O(n)	Use DENSE_RANK to efficiently identify consecutive login streaks in one pass
Window Function with Row Number	O(n log n)	O(n)	Use ROW_NUMBER() to detect consecutive date patterns efficiently

Brute Force (Self-Join Approach) — Algorithm Steps

Get distinct login dates for each user to avoid duplicate entries
Self-join the logins table 4 times to check for consecutive days
Match dates that are exactly 1, 2, 3, and 4 days apart
Join with Accounts table to get user names
Group results to avoid duplicates

Visualization

Tap to expand

Step-by-Step Walkthrough

Create distinct login dates

Remove duplicate login dates for each user

Self-join operations

Join the same table 5 times to check consecutive dates

Date difference validation

Verify each join has exactly 1, 2, 3, 4 day differences

Result aggregation

Collect users who have complete 5-day sequences

Code -

solution.c — C

// SQL Query for Brute Force Approach
char* query = "\
SELECT DISTINCT a.id, a.name \
FROM Accounts a \
JOIN ( \
    SELECT DISTINCT l1.id \
    FROM Logins l1, Logins l2, Logins l3, Logins l4, Logins l5 \
    WHERE l1.id = l2.id AND l1.id = l3.id AND l1.id = l4.id AND l1.id = l5.id \
    AND DATEDIFF(l2.login_date, l1.login_date) = 1 \
    AND DATEDIFF(l3.login_date, l1.login_date) = 2 \
    AND DATEDIFF(l4.login_date, l1.login_date) = 3 \
    AND DATEDIFF(l5.login_date, l1.login_date) = 4 \
) active_users ON a.id = active_users.id \
ORDER BY a.id";

Time & Space Complexity

Time Complexity

⏱️

O(n⁵)

Five nested scans through the login data for each self-join operation

✓ Linear Growth

Space Complexity

O(n)

Space for storing intermediate join results

⚡ Linearithmic Space

Constraints

1 ≤ Accounts table size ≤ 500
1 ≤ Logins table size ≤ 1000
1 ≤ id ≤ 500
All login_date values are valid dates
A user may have multiple login records for the same date

73.8K Views

High Frequency

~15 min Avg. Time

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

Input & Output

Visualization

Time & Space Complexity

Related Problems

Constraints

Common Approaches

Brute Force (Self-Join Approach) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Constraints

Select Compiler