Skip to main content
Talentd

New Users Daily Count - Solution & Explanation

MediumPremiumFree on FleetCodeDatabase4 min readAsked at: LinkedIn
Practice this problem

Problem Statement

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.

Approach Overview

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.

Solution

Code

MySQL

MySQL

Try this approach in the editor →

Detailed Complexity Analysis

ApproachTimeSpaceWhen to Use
MIN() Aggregation + GROUP BYO(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.

Video Solution

LINKEDIN LeetCode Medium “New Users Daily Count" 1107 Interview SQL Question Explanation | EDS • Everyday Data Science • 1,718 views views

Frequently Asked Questions

Is New Users Daily Count easy or hard?
The problem is considered medium difficulty because it requires identifying the first occurrence per user before performing aggregation. Many candidates initially count all logins per day instead of isolating first logins.
New Users Daily Count Python/Java solution
This problem is primarily a SQL database question, so Python or Java implementations are uncommon. If implemented programmatically, you would track the earliest login per user using a hash map and then aggregate counts by date.
How to solve New Users Daily Count in O(n)?
Filter records to activity = 'login', compute MIN(activity_date) grouped by user_id to identify each user's first login, then group those first-login dates and count users per day. Finally filter the result to the required 90‑day window ending on 2019‑06‑30.
What is the best approach for New Users Daily Count?
The best approach is computing each user's first login date using MIN(activity_date) after filtering to login events. Then group those first-login rows by date and count users. This runs in O(n) time with a single aggregation scan in SQL and is the most common MySQL interview solution.
Is New Users Daily Count asked at Google/Amazon/Meta?
Problems involving first events, daily active users, and cohort-style metrics frequently appear in SQL interviews at companies like Amazon, Meta, and Google. Variations of this problem test aggregation, window functions, and time-based filtering.
What data structure is used in New Users Daily Count?
In SQL terms, the solution relies on relational table scans combined with aggregation structures used internally by the database engine. Conceptually it uses grouping by user_id and date along with MIN aggregation or window functions like ROW_NUMBER.
What is the time complexity of New Users Daily Count?
The SQL query typically runs in O(n) time where n is the number of rows in the activity table. The database scans login events, computes a MIN aggregation per user, and groups results by date. Space complexity is O(n) for intermediate grouped results depending on execution strategy.

Ready to solve this problem?

Practice New Users Daily Count with our built-in code editor and test cases.

Practice on FleetCode
Previous

Combine Two Tables

Next

Second Highest Salary