Session Pulse

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

Problem

In user_sessions each row is one session, but some sessions have no duration on file and are left out of every figure. From the rest, summarize each user's engagement as their average session length, how many sessions they logged and their longest one, keeping users with at least three sessions, longest average first.

Engagement is slipping. Who is phoning it in?

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

Two filters, two levels

This is a filter-scoping question dressed up as an engagement summary. Every candidate writes the per-user average, count and max. What separates them is where each rule lives. The missing-duration rule is about individual sessions, so it has to act before anything is summarized. The three-session floor is about a user's total, so it can only be judged afterwards. Trust AVG to skip the NULLs and leave the rows in, and COUNT(*) still counts the untimed sessions: eight users with only two real sessions get past the floor, and the answer has 48 rows where it should have 40.

Step 1: Drop untimed sessions before anything is counted

WHERE session_duration_sec IS NOT NULL runs once per row, before grouping, so AVG, COUNT(*) and MAX all see only timed sessions. AVG and MAX would ignore the NULL anyway. COUNT(*) would not, and the count is exactly what the floor reads.

Step 2: Collapse to one row per user_id

GROUP BY user_id gives one bucket per user, and the three figures come out of the same pass: AVG(session_duration_sec) as avg_duration, COUNT(*) as session_count, MAX(session_duration_sec) as longest.

Step 3: Judge the floor on the user's tally

Three sessions is a property of the bucket, not of any one row, so it goes in HAVING COUNT(*) >= 3. Because the row filter already ran, that count is timed sessions only, which is what the floor means.

Step 4: Sort by the average, longest first

ORDER BY avg_duration DESC sorts on the alias the query returns. Users whose averages tie may come back in either order; the grade only checks the sort.

SELECT user_id, AVG(session_duration_sec) AS avg_duration, COUNT(*) AS session_count, MAX(session_duration_sec) AS longest
FROM user_sessions
WHERE session_duration_sec IS NOT NULL
GROUP BY user_id
HAVING COUNT(*) >= 3
ORDER BY avg_duration DESC

Row filter left out

No WHERE. AVG and MAX come out right, but COUNT(*) counts every row, so a user with two timed sessions and one blank one reads as 3 and passes HAVING COUNT(*) >= 3. That is eight extra users on this data, and session_count is too high for five of the real ones.

Row filter in place

WHERE session_duration_sec IS NOT NULL removes the blank rows first, so all three aggregates and the floor agree on the same set of sessions. 40 users qualify.

Common follow-up questions

• User 100 has three sessions on overlapping devices the same afternoon. How would you report engaged time instead of summed session length? (Tests interval merging on `session_start` plus `session_duration_sec`.)
• Product wants users under the floor kept, but flagged rather than dropped. What changes? (Tests moving a group filter into a computed column.)
• The floor becomes three timed sessions in the last 30 days. Where does that window go? (Tests that a time window is a row filter, applied before the tally.)