Skip to main content
Talentd

Find Active Users - Solution & Explanation

MediumPremiumFree on FleetCodeDatabase4 min read
Practice this problem

Problem Statement

Table: Users

+-------------+----------+ 
| Column Name | Type     | 
+-------------+----------+ 
| user_id     | int      | 
| item        | varchar  |
| created_at  | datetime |
| amount      | int      |
+-------------+----------+
This table may contain duplicate records. 
Each row includes the user ID, the purchased item, the date of purchase, and the purchase amount.

Write a solution to identify active users. An active user is a user that has made a second purchase within 7 days of any other of their purchases.

For example, if the ending date is May 31, 2023. So any date between May 31, 2023, and June 7, 2023 (inclusive) would be considered "within 7 days" of May 31, 2023.

Return a list of user_id which denotes the list of active users in any order.

The result format is in the following example.

 

Example 1:

Input:
Users table:
+---------+-------------------+------------+--------+ 
| user_id | item              | created_at | amount |  
+---------+-------------------+------------+--------+
| 5       | Smart Crock Pot   | 2021-09-18 | 698882 |
| 6       | Smart Lock        | 2021-09-14 | 11487  |
| 6       | Smart Thermostat  | 2021-09-10 | 674762 |
| 8       | Smart Light Strip | 2021-09-29 | 630773 |
| 4       | Smart Cat Feeder  | 2021-09-02 | 693545 |
| 4       | Smart Bed         | 2021-09-13 | 170249 |
+---------+-------------------+------------+--------+ 
Output:
+---------+
| user_id | 
+---------+
| 6       | 
+---------+
Explanation: 
- User with user_id 5 has only one transaction, so he is not an active user.
- User with user_id 6 has two transaction his first transaction was on 2021-09-10 and second transation was on 2021-09-14. The distance between the first and second transactions date is <= 7 days. So he is an active user. 
- User with user_id 8 has only one transaction, so he is not an active user.  
- User with user_id 4 has two transaction his first transaction was on 2021-09-02 and second transation was on 2021-09-13. The distance between the first and second transactions date is > 7 days. So he is not an active user.

Approach Overview

Problem Overview: The task is to identify users who were active for multiple consecutive days based on activity records stored in a database table. Each row represents a user action on a specific date. Your goal is to detect users whose activity spans at least five consecutive days and return their IDs.

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

A straightforward method checks whether a user has activity on consecutive dates by repeatedly joining the table to itself with date offsets. For example, join rows where date = date + INTERVAL 1 DAY, +2 DAY, and so on until five consecutive days are verified. This approach works but becomes inefficient because the database must repeatedly scan and match rows for each user. It demonstrates the core idea of verifying consecutive activity but scales poorly for large datasets.

Approach 2: Window Function + Consecutive Grouping (O(n log n) time, O(n) space)

The efficient solution uses SQL window functions. First, partition records by user_id and order them by activity_date. Assign a row number using ROW_NUMBER(). The key trick is subtracting this row number from the date value to create a stable grouping key for consecutive days. When dates are consecutive, the difference between the date and the row index stays constant. Group by this calculated key and count rows within each group. If a group contains at least five records, that user has five consecutive active days.

This pattern is common in SQL problems involving consecutive sequences. Window functions allow you to scan the table once, track ordering, and detect runs of continuous values without repeated joins. Modern relational databases optimize these operations well, making the solution efficient for large datasets.

Recommended for interviews: The window function approach is what interviewers expect. The self‑join approach shows you understand how to check consecutive dates, but it is not scalable. Using ROW_NUMBER() with partitioning demonstrates strong SQL fundamentals and familiarity with sequence detection patterns in database problems and advanced SQL queries, especially when working with window functions.

Solution

Code

MySQL

MySQL

Try this approach in the editor →

Detailed Complexity Analysis

ApproachTimeSpaceWhen to Use
Self Join on Consecutive DatesO(n^2)O(1)Useful for understanding consecutive date checks when window functions are unavailable
Window Function with ROW_NUMBER GroupingO(n log n)O(n)Best general solution in modern SQL databases; efficient for detecting consecutive sequences

Video Solution

Leetcode MEDIUM 2688 - Find Active Users - LEAD Window Func SQL Explained by Everyday Data Science • Everyday Data Science • 528 views views

Frequently Asked Questions

Is Find Active Users easy or hard?
The problem is rated Medium because detecting consecutive sequences in SQL requires understanding window functions and grouping tricks. Developers familiar with ROW_NUMBER and date arithmetic usually solve it quickly, while beginners often struggle with the consecutive-day logic.
Find Active Users Python/Java solution
This is primarily a SQL database problem, so solutions are typically written in MySQL, PostgreSQL, or other relational SQL dialects. Python or Java would only be used to execute the query or process the result, while the core logic stays inside the SQL statement.
How to solve Find Active Users in O(n)?
Pure O(n) performance is uncommon in SQL because ordered window functions usually require sorting. However, after sorting the records by user_id and activity_date, the ROW_NUMBER grouping technique processes rows in a single pass to identify consecutive activity streaks.
What is the best approach for Find Active Users?
The best approach uses a SQL window function with ROW_NUMBER() partitioned by user_id and ordered by activity_date. By subtracting the row number from the date, consecutive days form a constant grouping key. Grouping by this key allows you to count streaks and detect users active for at least five consecutive days efficiently.
Is Find Active Users asked at Google/Amazon/Meta?
Consecutive activity detection is a common SQL interview pattern used by companies like Amazon, Meta, and Google. Variations often appear in analytics-style questions involving user engagement, login streaks, or retention metrics.
What data structure is used in Find Active Users?
The problem relies on relational database operations rather than traditional in-memory data structures. The main tools are SQL window functions, grouping operations, and ordered partitions that track sequences of dates for each user.
What is the time complexity of Find Active Users?
The optimized SQL solution runs in roughly O(n log n) time because the database sorts rows by user and date for the window function. After sorting, grouping and counting consecutive streaks is linear in the number of records.

Ready to solve this problem?

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

Practice on FleetCode
Previous

Combine Two Tables

Next

Second Highest Salary