Session Duration by Account Status

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

L3

Asked in

phData

Problem

Session length is logged per session in user_sessions while account status lives only on each person's users record, and a few sessions were never timed, so a blank duration is unknown rather than zero. An analyst checking whether account tier tracks engagement wants the average session duration in seconds for every account status.

Average session duration broken down by user account status

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

This is a cross-table average dressed up as an engagement study. The metric, session_duration_sec, sits on user_sessions; the bucket, account_status, exists only on users. Everyone writes the join. What separates candidates is what they do with the sessions that were never timed: 17 of the 200 sessions carry a NULL duration, and the reflex to tidy them with COALESCE(session_duration_sec, 0) turns unknown sessions into zero-second ones. Every status average drops, by an amount that depends on how many untimed sessions that status happens to hold, so part of the tier gap the analyst is chasing becomes an artifact of missing data.

Getting to the answer

Step 1: Find where each piece lives

user_sessions has the duration and a user_id, but no status. No filter or projection over sessions alone can bucket them by tier, so you bring in users on user_id. Because user_id is unique in users, each session matches exactly one user: the join adds a column without multiplying sessions.

Step 2: Average sessions, not users

GROUP BY u.account_status with AVG(us.session_duration_sec) weights every session once. Averaging per user first and then across users gives a person with one session the same say as someone with eight, which answers a different question.

Step 3: Let the average skip untimed sessions

AVG drops NULLs from both the sum and the count, which is exactly what 'unknown, not zero' means. The correct handling of the blanks is to add nothing.

SELECT u.account_status, AVG(us.session_duration_sec) AS avg_session_duration
FROM users u
INNER
JOIN user_sessions us ON u.user_id = us.user_id
GROUP BY u.account_status

SELECT u.account_status, AVG(COALESCE(us.session_duration_sec, 0)) AS avg_session_duration
FROM users u
JOIN user_sessions us ON u.user_id = us.user_id
GROUP BY u.account_status

Common follow-up questions

• How would you also report how many sessions in each status had no recorded duration? (Tests whether the candidate knows `COUNT(*)` and `COUNT(session_duration_sec)` differ exactly by the NULLs.)
• The analyst now wants the median duration per status instead of the mean. What changes? (Probes percentile aggregates and whether NULLs are still excluded.)
• If `users` kept a row per status change, how would you attribute each session to the status in force at `session_start`? (Tests a range join against a slowly changing dimension.)