New Users Daily Count

New Users Daily Count

What is this problem about?

The New Users Daily Count SQL problem asks you to report the number of users who logged in for the first time on each date within a specified date range. For each user, identify their earliest login date, then count how many users had their first login on each day (filtering to dates within the given range). This is a SQL aggregation problem combining window functions or GROUP BY with a date filter.

Why is this asked in interviews?

LinkedIn asks this to test the ability to compute "first occurrence per user" in SQL — a common analytics task in user acquisition reporting. It validates knowledge of MIN() aggregation or window functions for finding each user's first event, followed by counting and date filtering. The database interview pattern requires combining GROUP BY, MIN, and conditional filtering cleanly.

Algorithmic pattern used

Subquery + GROUP BY. Subquery: find the minimum login date per user — SELECT user_id, MIN(login_date) AS first_login FROM Logins GROUP BY user_id. Then outer query: filter to dates within the range and count users per date: SELECT first_login AS login_date, COUNT(*) AS user_count FROM subquery WHERE first_login BETWEEN start AND end GROUP BY first_login ORDER BY login_date.

Example explanation

Logins table: user 1 → dates [2019-05-01, 2019-05-02], user 2 → [2019-06-21], user 3 → [2019-05-01]. First logins: user 1 → 2019-05-01, user 2 → 2019-06-21, user 3 → 2019-05-01. Within range [2019-05-01, 2019-06-30]: count per date → 2019-05-01: 2, 2019-06-21: 1.

Common mistakes candidates make

• Counting all logins per date instead of only first logins.
• Forgetting the date range filter (including first logins outside the specified period).
• Not grouping by date in the outer query (returning individual user rows instead of aggregated counts).
• Using ROW_NUMBER() without properly filtering to rank=1.

Interview preparation tip

"First occurrence per user" is a very common SQL pattern in product analytics. Know two approaches: (1) MIN() GROUP BY in a subquery, then filter and aggregate; (2) Window function ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY date ASC) then filter WHERE row_num=1. Practice both — interviewers may ask you to rewrite one using the other. Date range filtering with BETWEEN is clean and readable; always double-check inclusive boundary semantics.