Table: Traffic
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| user_id | int |
| activity | enum |
| activity_date | date |
+---------------+---------+
This table may have duplicate rows.
The activity column is an ENUM (category) type of ('login', 'logout', 'jobs', 'groups', 'homepage').
Write a solution to reports for every date within at most 90 days from today, the number of users that logged in for the first time on that date. Assume today is 2019-06-30.
Return the result table in any order.
The result format is in the following example.
Example 1:
Input: Traffic table: +---------+----------+---------------+ | user_id | activity | activity_date | +---------+----------+---------------+ | 1 | login | 2019-05-01 | | 1 | homepage | 2019-05-01 | | 1 | logout | 2019-05-01 | | 2 | login | 2019-06-21 | | 2 | logout | 2019-06-21 | | 3 | login | 2019-01-01 | | 3 | jobs | 2019-01-01 | | 3 | logout | 2019-01-01 | | 4 | login | 2019-06-21 | | 4 | groups | 2019-06-21 | | 4 | logout | 2019-06-21 | | 5 | login | 2019-03-01 | | 5 | logout | 2019-03-01 | | 5 | login | 2019-06-21 | | 5 | logout | 2019-06-21 | +---------+----------+---------------+ Output: +------------+-------------+ | login_date | user_count | +------------+-------------+ | 2019-05-01 | 1 | | 2019-06-21 | 2 | +------------+-------------+ Explanation: Note that we only care about dates with non zero user count. The user with id 5 first logged in on 2019-03-01 so he's not counted on 2019-06-21.
Problem Overview: The table records multiple user activities such as login, logout, and page visits. The task is to compute how many new users logged in each day during the last 90 days (ending at 2019‑06‑30). A user counts as new only on the date of their first login.
Approach 1: First Login Aggregation with MIN() (O(n) time, O(n) space)
Filter the table to only login events, then determine the earliest login date per user using MIN(activity_date). This identifies each user's first appearance in the system. Once you have that date, group the results by the first login day and count users per date. Finally, restrict the results to the required 90‑day window using a date filter. The database performs a single scan and aggregation, making it efficient for large datasets and the most common solution in SQL interview questions.
Approach 2: Window Function with ROW_NUMBER() (O(n) time, O(n) space)
Another option uses a window function to mark the first login per user. After filtering to activity = 'login', apply ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY activity_date). The row with row_number = 1 represents the user's first login. From there, group those rows by date and count users, again filtering to the 90‑day interval. Window functions make the logic explicit and are common in analytical queries across modern database systems.
Both strategies rely on the same insight: identifying the first login event per user before performing the daily aggregation. Without isolating that first event, users with multiple logins would be counted repeatedly. SQL aggregation or window functions handle this cleanly.
Recommended for interviews: The MIN(activity_date) aggregation approach is usually expected. It shows you understand grouping, filtering, and how to derive first occurrences using SQL aggregates. Mentioning the window function alternative demonstrates deeper SQL knowledge, but the aggregation solution is simpler and widely supported.
MySQL
| Approach | Time | Space | When to Use |
|---|---|---|---|
| MIN() Aggregation + GROUP BY | O(n) | O(n) | Standard SQL solution. Best when identifying first occurrence per user. |
| Window Function (ROW_NUMBER) | O(n) | O(n) | Useful when window functions are supported and clearer analytical queries are preferred. |
LINKEDIN LeetCode Medium “New Users Daily Count" 1107 Interview SQL Question Explanation | EDS • Everyday Data Science • 1,650 views views
Practice New Users Daily Count with our built-in code editor and test cases.
Practice on FleetCode