Skip to main content
Talentd

Active Users - Solution & Explanation

MediumPremiumFree on FleetCodeDatabase5 min readAsked at: Ciena
Practice this problem

Problem Statement

Table: Accounts

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| name          | varchar |
+---------------+---------+
id is the primary key (column with unique values) for this table.
This table contains the account id and the user name of each account.

 

Table: Logins

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| login_date    | date    |
+---------------+---------+
This table may contain duplicate rows.
This table contains the account id of the user who logged in and the login date. A user may log in multiple times in the day.

 

Active users are those who logged in to their accounts for five or more consecutive days.

Write a solution to find the id and the name of active users.

Return the result table ordered by id.

The result format is in the following example.

 

Example 1:

Input: 
Accounts table:
+----+----------+
| id | name     |
+----+----------+
| 1  | Winston  |
| 7  | Jonathan |
+----+----------+
Logins table:
+----+------------+
| 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 |
+----+----------+
Explanation: 
User Winston with id = 1 logged in 2 times only in 2 different days, so, Winston is not an active user.
User Jonathan with id = 7 logged in 7 times in 6 different days, five of them were consecutive days, so, Jonathan is an active user.

 

Follow up: Could you write a general solution if the active users are those who logged in to their accounts for n or more consecutive days?

Approach Overview

Problem Overview: The task is to find users who logged into the system for five or more consecutive days. The Logins table stores login dates for each user, and the final result must return the user id and name from the Accounts table for users meeting the consecutive activity requirement.

Approach 1: Self Join for Consecutive Dates (O(n^2) time, O(1) space)

A direct approach checks consecutive days by joining the Logins table with itself multiple times. For each login record, you attempt to match entries for the next four days using date arithmetic like login_date + INTERVAL 1 DAY. If all five rows exist for the same user, the user qualifies as active. This approach relies entirely on joins and date comparisons, which makes the logic straightforward but inefficient when the table grows. Multiple joins on the same dataset lead to quadratic behavior, making it impractical for large datasets.

Approach 2: Window Functions with ROW_NUMBER (O(n log n) time, O(n) space)

The optimized solution uses a window function to detect consecutive date sequences. Partition the Logins table by user_id and assign a ROW_NUMBER() ordered by login_date. For each row, compute a grouping key such as DATE_SUB(login_date, INTERVAL row_number DAY). Consecutive dates produce the same key because the offset between the row number and the date stays constant. Group by user_id and this derived key, then count the rows in each group. Any group with COUNT(*) >= 5 represents five consecutive login days. Finally, join the result with Accounts to return the user names.

This pattern is common in SQL problems that involve detecting consecutive events. The window function eliminates complex joins and transforms the problem into grouping sequences after sorting. MySQL performs an internal sort for the window operation, which leads to O(n log n) time complexity and O(n) additional space for intermediate results.

Recommended for interviews: The window function approach is the expected solution. Interviewers want to see that you recognize the consecutive-sequence pattern and solve it using window functions and grouping. Mentioning the self-join method shows you understand the brute-force logic, but using ROW_NUMBER() demonstrates stronger SQL skills and familiarity with advanced database querying techniques.

Solution

First, we join the Logins table and the Accounts table, and remove duplicates to get the temporary table T.

Then, we use the window function ROW_NUMBER() to calculate the base login date g for each user id. If a user logs in for 5 consecutive days, their g values are the same.

Finally, we group by id and g to count the number of logins for each user. If the number of logins is greater than or equal to 5, then the user is considered active.

Code

MySQL

MySQL

Try this approach in the editor →

Detailed Complexity Analysis

ApproachTimeSpaceWhen to Use
Self Join for Consecutive DaysO(n^2)O(1)Small datasets or when window functions are unavailable
Window Function (ROW_NUMBER + Date Grouping)O(n log n)O(n)Preferred solution for SQL interviews and production queries

Video Solution

LeetCode 1454: Active Users [SQL] • Frederik Müller • 6,939 views views

Watch 1 more video solutions →

Frequently Asked Questions

Is Active Users easy or hard?
Active Users is generally considered a medium-difficulty SQL problem. The challenge is recognizing the consecutive-sequence pattern and applying window functions with date normalization to group login streaks efficiently.
Active Users Python/Java solution
This problem is typically solved using SQL rather than Python or Java because it operates directly on relational tables. The expected solution uses SQL window functions like ROW_NUMBER() combined with grouping to detect consecutive login dates.
How to solve Active Users in O(n)?
Pure O(n) solutions are difficult in SQL because ordering by date is required to detect consecutive days. Most practical implementations rely on window functions or sorting, leading to O(n log n) complexity. Using ROW_NUMBER() with date normalization is the standard optimized method.
What is the best approach for Active Users?
The most efficient approach uses SQL window functions. Assign ROW_NUMBER() partitioned by user_id and ordered by login_date, then group rows using DATE_SUB(login_date, INTERVAL row_number DAY). Any group with at least five rows represents five consecutive login days. This solution runs in O(n log n) time due to sorting.
Is Active Users asked at Google/Amazon/Meta?
Database problems involving consecutive events and login streaks frequently appear in interviews at companies like Amazon, Meta, and Google. Variations of this question test SQL window functions, date arithmetic, and grouping logic.
What data structure is used in Active Users?
The problem primarily relies on SQL window functions rather than traditional data structures. ROW_NUMBER() creates ordered sequences within each user partition, and grouping on a normalized date value identifies consecutive login streaks.
What is the time complexity of Active Users?
The optimal SQL solution using window functions runs in O(n log n) time because the database sorts rows within each user partition for the ROW_NUMBER() calculation. Space complexity is O(n) for intermediate results during grouping.

Ready to solve this problem?

Practice Active Users with our built-in code editor and test cases.

Practice on FleetCode
Previous

Combine Two Tables

Next

Second Highest Salary