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 - SQL Problem INPUT Accounts Table id name 1 Alice 2 Bob 3 Charlie Logins Table id login_date 1 2024-01-01 1 2024-01-02 1 2024-01-03 1 2024-01-04 1 2024-01-05 ... more rows ... ALGORITHM STEPS 1 Remove Duplicates DISTINCT on (id, login_date) 2 Add Row Numbers ROW_NUMBER() per user 3 Calculate Group Key login_date - row_num = grp 4 Count Consecutive GROUP BY id, grp HAVING 5+ Consecutive Group Detection date row date-row 01-01 1 12-31 01-02 2 12-31 01-03 3 12-31 Same grp! FINAL RESULT Active Users (5+ consecutive days) id name 1 Alice OK - Found! Output Array: [{id: 1, name: "Alice"}] Ordered by id Alice logged in 5+ days consecutively Bob, Charlie did not Key Insight: The "date minus row_number" trick creates identical values for consecutive dates. If dates are consecutive (01, 02, 03) and row numbers are (1, 2, 3), then date-row gives same result. Group by this value and count to find streaks of 5+ consecutive login days per user. TutorialsPoint - Active Users | Optimal Solution

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