New Users Daily Count - Practice Coding Problems

New Users Daily Count - Problem

Database Medium

New Users Daily Count

You're working as a data analyst for a social platform and need to track first-time user logins within the last 90 days.

Given a Traffic table that logs user activities:

Column Name	Type
user_id	int
activity	enum
activity_date	date

The activity column contains: ('login', 'logout', 'jobs', 'groups', 'homepage')

Your task: For every date within the last 90 days from today (2019-06-30), count how many users logged in for the very first time on that date.

🎯 Key Challenge: A user might have multiple login records, but you only care about their first login date.

Return: A report showing login_date and user_count for new users on each date within the 90-day window.

Input & Output

example_1.sql — Basic Example

$ 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 | login | 2019-06-21 | | 3 | logout | 2019-06-21 |

› Output: | login_date | user_count | |------------|------------| | 2019-05-01 | 1 | | 2019-06-21 | 1 |

💡 Note: User 1's first login: 2019-05-01 (within 90 days). User 2's first login: 2019-06-21 (within 90 days). User 3's first login: 2019-01-01 (outside 90-day window, excluded). Only dates with new users are shown.

example_2.sql — Multiple Users Same Date

$ Input: Traffic table: | user_id | activity | activity_date | |---------|----------|---------------| | 1 | login | 2019-06-30 | | 2 | login | 2019-06-30 | | 3 | login | 2019-06-30 | | 4 | login | 2019-06-29 | | 4 | login | 2019-06-30 |

› Output: | login_date | user_count | |------------|------------| | 2019-06-29 | 1 | | 2019-06-30 | 3 |

💡 Note: Three users (1, 2, 3) had their first login on 2019-06-30. User 4's first login was 2019-06-29 (their login on 2019-06-30 doesn't count as it's not their first). Result shows count of new users per date.

example_3.sql — Edge Case - No New Users

$ Input: Traffic table: | user_id | activity | activity_date | |---------|----------|---------------| | 1 | login | 2019-01-01 | | 1 | login | 2019-06-30 | | 2 | homepage | 2019-06-30 | | 2 | logout | 2019-06-29 |

› Output: (Empty result set)

💡 Note: User 1's first login was 2019-01-01 (outside 90-day window). User 2 never logged in (only homepage and logout activities). No users had their first login within the specified date range, so result is empty.

Visualization

Tap to expand

Understanding the Visualization

Collect All Transactions

Gather all customer card scans (like login activities in our Traffic table)

Find First Visit Date

For each customer, identify their very first visit (minimum date)

Filter Recent Period

Keep only first visits within the last 90 days

Count New Customers Daily

Group by date and count how many customers joined each day

Key Takeaway

🎯 Key Insight: Use GROUP BY with MIN() to find first login dates efficiently, then filter by date range - much faster than checking each date individually!

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

Single scan of n records plus sorting for window function

⚡ Linearithmic

Space Complexity

O(n)

Space for storing intermediate results and window function processing

⚡ Linearithmic Space

Constraints

1 ≤ Traffic table rows ≤ 10⁵
user_id is a positive integer
activity ∈ {'login', 'logout', 'jobs', 'groups', 'homepage'}
Date range: exactly 90 days from 2019-04-02 to 2019-06-30
Multiple rows per user are allowed (duplicate activities on same/different dates)

Asked in

f Meta 45 a Amazon 38 G Google 32 ⊞ Microsoft 28

The optimal solution uses GROUP BY with MIN() to find each user's first login date in O(n log n) time. Key insight: filter for login activities first, then use date range filtering after finding minimum dates to avoid repeated calculations.

Common Approaches

Approach	Time	Space	Notes
✓ Brute Force (Subquery for Each Date)	O(d × n²)	O(d)	For each date, check all users to see if it was their first login
Optimized Window Function (Optimal)	O(n log n)	O(n)	Use window functions to find first login dates efficiently in one pass

Brute Force (Subquery for Each Date) — Algorithm Steps

Generate all dates within the 90-day window from 2019-06-30
For each date, find users who had their first login on that date
Use correlated subquery to check if the current date equals user's minimum login date
Count the users for each date

Visualization

Tap to expand

Step-by-Step Walkthrough

Generate Date Range

Create all 90 dates from 2019-04-02 to 2019-06-30

For Each Date

Check every user to see if this date was their first login

Correlated Subquery

Find MIN(activity_date) for each user repeatedly

Count New Users

Count users whose first login equals current date

Code -

solution.c — C

// Brute Force SQL Solution
char* sqlQuery = "\
WITH RECURSIVE date_range AS (\
    SELECT DATE('2019-06-30') - INTERVAL 89 DAY AS date_val\
    UNION ALL\
    SELECT date_val + INTERVAL 1 DAY\
    FROM date_range\
    WHERE date_val < '2019-06-30'\
)\
SELECT \
    dr.date_val AS login_date,\
    COALESCE(COUNT(DISTINCT t.user_id), 0) AS user_count\
FROM date_range dr\
LEFT JOIN Traffic t ON t.activity_date = dr.date_val \
    AND t.activity = 'login'\
    AND t.activity_date = (\
        SELECT MIN(t2.activity_date)\
        FROM Traffic t2\
        WHERE t2.user_id = t.user_id \
        AND t2.activity = 'login'\
    )\
GROUP BY dr.date_val\
ORDER BY dr.date_val;\
";

Time & Space Complexity

Time Complexity

⏱️

O(d × n²)

d dates × n users, with each user requiring scan of n records

⚠ Quadratic Growth

Space Complexity

O(d)

Space for storing results for d dates

✓ Linear Space

Constraints

1 ≤ Traffic table rows ≤ 10⁵
user_id is a positive integer
activity ∈ {'login', 'logout', 'jobs', 'groups', 'homepage'}
Date range: exactly 90 days from 2019-04-02 to 2019-06-30
Multiple rows per user are allowed (duplicate activities on same/different dates)

41.2K Views

High Frequency

~15 min Avg. Time

1.8K 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

Input & Output

Visualization

Time & Space Complexity

Related Problems

Constraints

Common Approaches

Brute Force (Subquery for Each Date) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Constraints

Select Compiler