Daily Session and User Counts

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

Domain

SQL

Difficulty

easy

Seniority

L3

Asked in

Meta

Problem

For each day in user_sessions, how many sessions started and how many different users were behind them? An analyst is laying the figures out as a daily engagement timeline, earliest day first.

Sessions and users, day by day.

Tables

user_sessions

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

Expected output

Worked solution and explanation

This is a grain question wearing a dashboard costume. Every row in user_sessions stamps an hour, and the dashboard wants a day, so the first job is throwing the clock time away. The second job is the one that separates candidates: two numbers at that grain, one counting every session and one counting each person once. Write the same count twice and the user column quietly equals the session column on any day someone comes back. On this data that day is 2026-06-15, where user 100 opens three overlapping sessions and a sloppy query claims 4 users instead of 2.

Getting to the query

Step 1: Bucket on the day, not the instant

session_start holds text like '2026-06-15 10:00:00'. Grouping on it as stored gives one bucket per start time, so the daily report becomes a list of sessions. DATE(session_start) drops the time and makes the calendar day the key, which is the thing the dashboard plots.

Step 2: Count sessions and people differently

Every row is one session, so COUNT(*) is the session total. People repeat across rows, so the user figure needs COUNT(DISTINCT user_id). Both sit in the same GROUP BY, which is why getting the grain right in step one fixes both metrics at once.

Step 3: Read it as a timeline

ORDER BY session_date puts the oldest day at the top. The date is unique per output row, so there is no tie left to break.

SELECT
    DATE(session_start) AS session_date,
    COUNT(*) AS total_sessions,
    COUNT(DISTINCT user_id) AS unique_users
FROM user_sessions
GROUP BY DATE(session_start)
ORDER BY session_date

Counting rows twice

COUNT(*) for both columns. On 2026-06-15 this reports 4 sessions and 4 users, and the dashboard can never show anyone coming back.

Counting rows, then people

COUNT(*) beside COUNT(DISTINCT user_id). The same day reports 4 sessions from 2 users, and the gap is exactly the repeat visits the dashboard exists to show.

Common follow-up questions

• How would you compute the average sessions per user per day? (Divide `total_sessions` by `unique_users`, casting first so integer division does not floor it.)
• How would you keep only the days where sessions per user exceeded 5? (A group filter on the ratio of the two counts, with the same integer-division care.)
• Why can't a weekly unique-user count be summed from the daily figures? (A user active on two days is counted twice in the sum; the distinct count has to run over the whole week.)
• What if the dashboard needed hourly granularity? (Truncate `session_start` to the hour instead of the day, with something like `DATE_TRUNC('hour', session_start)`.)