Session Overview

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

Problem

Accounts that never logged a session still belong in the report with a count of zero, and every account is its own row even where two usernames differ only in letter case. For each user in users, the product team wants the user_id and username next to how many sessions that user has in user_sessions and their longest session_duration_sec.

Full engagement picture, even for the ones who never showed up.

Tables

users

Columns: user_id INTEGER, username TEXT, email TEXT, signup_date TEXT, account_status TEXT, age_bucket TEXT

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

What this problem really is

This is an outer-join aggregate wearing an engagement-report costume, and it has two traps stacked on top of each other. Everyone writes the join and the COUNT. What separates candidates is whether the users with zero sessions survive, and whether they come out as 0 or as 1. An INNER JOIN quietly deletes every session-less account, so 'abel' never appears. A LEFT JOIN with COUNT(*) keeps 'abel' but reports one session, because the outer join manufactured a row for him. Either way, the inactive accounts Product explicitly asked about are exactly the rows that come out wrong.

There is a third, quieter trap in the grain. The users table holds 'alice' and 'Alice' as two different accounts with different user_id values. Collapse on username and those two people blur into whatever your engine's string comparison decides. The account is the user_id; the name is just a label you carry along.

Step 1: Drive the query from users

The report is about every account, so users is the preserved side. Put it first and LEFT JOIN user_sessions on user_id. Accounts with no sessions come through once, with every s. column NULL.

Step 2: Collapse on user_id, carry username

Group by u.user_id, u.username. The user_id is what makes each row one account; username rides along because it is functionally determined by the key. This is what keeps 'alice' and 'Alice' as two rows instead of one muddled one.

Step 3: Count the joined column, not the row

COUNT(s.session_id) skips NULLs, so the phantom row an unmatched user gets contributes nothing and the count is 0. COUNT(*) counts that phantom row and reports 1. Same query shape, one token different, opposite answer for the population that matters.

Step 4: Let MAX return NULL for nobody

MAX(s.session_duration_sec) ignores NULLs too, so a user with no sessions gets NULL, which is the honest answer: there is no longest session. Do not paper over it with 0; a zero-second session is a real event, and a dashboard cannot tell the two apart.

SELECT
    u.user_id,
    u.username,
    COUNT(s.session_id) AS session_count,
    MAX(s.session_duration_sec) AS longest_session_sec
FROM users u
LEFT JOIN user_sessions s
    ON s.user_id = u.user_id
GROUP BY u.user_id, u.username

COUNT(*) after the outer join

Every unmatched user gets one all-NULL row from the LEFT JOIN, and COUNT(*) counts it. 'abel' shows session_count 1 with a NULL longest session: a contradiction sitting right in the output.

COUNT of the joined key

COUNT(s.session_id) only counts rows where a session actually matched. 'abel' shows 0 and NULL, which is what happened.

Common follow-up questions

• Product now wants only sessions from the last 30 days, but still every account. Where does the date condition go? (Tests whether the candidate moves the predicate into the `ON` clause to keep the outer join intact.)
• A user has sessions but every one has a `NULL` `session_duration_sec`. What do your two columns show, and is that right? (Tests understanding that `COUNT` and `MAX` each ignore `NULL`s independently.)
• How would you rewrite this to aggregate `user_sessions` first, and what changes in the zero-session case? (Tests the pre-aggregation pattern and the `COALESCE` it then requires.)