Active Users - Problem

You are given two tables: Accounts and Logins.

The Accounts table contains account information:

  • id (int): The primary key representing the account ID
  • name (varchar): The user name of each account

The Logins table contains login records:

  • id (int): The account ID of the user who logged in
  • login_date (date): The date when the login occurred

Note: The Logins table may contain duplicates as a user can log in multiple times in the same day.

Write an SQL query to find the id and name of active users.

Active users are defined as users who logged in to their accounts for 5 or more consecutive days.

Return the result table ordered by the id.

Table Schema

Accounts
Column Name Type Description
id PK int Primary key, account identifier
name varchar User name for the account
Primary Key: id
Logins
Column Name Type Description
id int Account ID (foreign key to Accounts)
login_date date Date when the user logged in
Primary Key: None

Input & Output

Example 1 — Active User with 5 Consecutive Days
Input Tables:
Accounts
id name
1 Winston
7 Jonathan
Logins
id login_date
7 2020-05-30
1 2020-05-30
7 2020-05-31
7 2020-06-01
7 2020-06-02
7 2020-06-02
7 2020-06-03
1 2020-06-07
7 2020-06-10
Output:
id name
7 Jonathan
💡 Note:

Jonathan (id=7) logged in on 5 consecutive days: 2020-05-30, 2020-05-31, 2020-06-01, 2020-06-02, and 2020-06-03. Note that duplicate logins on the same day (2020-06-02) are counted as one day. Winston (id=1) only logged in on 2 non-consecutive days, so he doesn't qualify as an active user.
Example 2 — No Active Users
Input Tables:
Accounts
id name
1 Alice
2 Bob
Logins
id login_date
1 2020-01-01
1 2020-01-02
1 2020-01-04
2 2020-01-01
2 2020-01-03
Output:
id name
💡 Note:

Neither Alice nor Bob has 5 consecutive login days. Alice logged in on 2020-01-01, 2020-01-02, and 2020-01-04 (gap on 2020-01-03). Bob only logged in on 2 non-consecutive days. Therefore, no users qualify as active users.

Constraints

  • 1 ≤ Accounts.id ≤ 100
  • 1 ≤ Logins.id ≤ 100
  • 1 ≤ Accounts.name.length ≤ 10
  • login_date is a valid date between 2020-01-01 and 2020-12-31

Visualization

Tap to expand
Active Users: Finding 5+ Consecutive Login DaysAccountsidname1Winston7JonathanLoginsidlogin_date705-30705-31706-01706-02706-03Window FunctionConsecutive DetectionActive Usersidname7JonathanJonathan has 5 consecutive login days (05-30 to 06-03)Winston only has 2 non-consecutive daysROW_NUMBER() creates groups → Count consecutive days → Filter >= 5 days
Understanding the Visualization
1
Input Tables
Accounts and Logins tables with user data
2
Window Function
ROW_NUMBER creates consecutive date groups
3
Output
Active users with 5+ consecutive days
Key Takeaway
🎯 Key Insight: Use ROW_NUMBER() with date arithmetic to group consecutive dates, then count and filter for streaks of 5+ days

Related Problems

Asked in
Facebook 8 Amazon 6 Microsoft 4
23.4K Views
Medium Frequency
~20 min Avg. Time
892 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