First Time Learners Per Day

A medium SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain: SQL
Difficulty: medium
Seniority: L4
Asked in: JPMorgan Chase

Problem

Every row in user_sessions is one session stamped with the moment it began, and most users come back for many sessions spread over many days. Count how many new users each calendar day brought in, where a user is new only on the day of their very first session.

Brand new users, day by day.

Tables

user_sessions

Columns: session_id INTEGER, user_id INTEGER, session_start TEXT, session_duration_sec INTEGER, device_id INTEGER, pages_viewed INTEGER

user_sessions: 10 of 200 rows
session_id	user_id	device_id	session_start	session_duration_sec	pages_viewed
7061	197	230	2026-02-02 01:00:00	53	3
7122	294	259	2026-03-03 02:00:00	76	6
7183	391	288	2026-04-04 03:00:00	99	9
7244	488	317	2026-05-05 04:00:00	122	12
7305	585	346	2026-06-06 05:00:00	145	15
7366	682	375	2026-07-07 06:00:00	168	18
7427	779	404	2026-08-08 07:00:00	191	21
7488	876	NULL	2026-09-09 08:00:00	214	24
7549	973	462	2026-01-10 09:00:00	237	27
7610	1070	491	2026-02-11 10:00:00	260	30

Expected output

First 5 of 58 rows
first_session_date	new_user_count
2023-01-01	2
2023-01-05	1
2023-01-09	1
2023-01-25	1
2023-05-01	1

Worked solution and explanation

Why this problem exists in real interviews

Counting distinct first events per day is a core growth metric: DAU new-user acquisition, cohort sizing, retention windows. Interviewers use it to test whether you reach for MIN() inside a CTE versus a self-join or window function, and whether you understand why a simple GROUP BY session_start gets it wrong.

Break down the requirements

Step 1: Find each user's earliest session date

MIN(DATE(session_start)) AS first_session_date grouped by user_id. DATE() strips the time component so sessions on the same day collapse.

Step 2: Count first-time users per date

Outer GROUP BY first_session_date with COUNT(*). Each user appears exactly once in the CTE, so COUNT(*) is the new-user count for that day.

Step 3: Order chronologically

ORDER BY first_session_date makes the output a readable growth timeline.

The solution

MIN() inside a CTE then aggregate

WITH first_sessions AS (
  SELECT user_id, MIN(DATE(session_start)) AS first_session_date
  FROM user_sessions
  GROUP BY user_id
)
SELECT first_session_date, COUNT(*) AS new_user_count
FROM first_sessions
GROUP BY first_session_date
ORDER BY first_session_date

Common follow-up questions

How would you compute 7-day and 30-day rolling new-user counts? (Tests window functions over the date-level result.)
What if a user can have sessions across multiple products - how do you find first-time learners per product per day? (Adds a PARTITION BY product dimension to the MIN() step.)
Rewrite without a CTE using a window function. (Tests `ROW_NUMBER`() OVER (PARTITION BY `user_id` ORDER BY `session_start`) = 1 as an alternative.)