On Their Way Out

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

L4

Asked in

City and County of San Francisco

Problem

We're compiling the users who signed up but never really engaged. Add up the pages each user viewed across all of their sessions, and return those whose lifetime total lands between 1 and 9 inclusive, alongside that total, quietest first.

They signed up. They never really showed up.

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

What this is really testing

This is a per-user total with a range filter on the total, wearing a churn-detection costume. The whole problem lives in one decision: the 1-to-9 bound is a property of the summed pages across a user's sessions, not of any single session. Anyone can write the GROUP BY. The tell is whether the range predicate lands in HAVING (after the sum) or leaks into WHERE (before it). Put it in WHERE and you filter individual low-page sessions, then sum the survivors, and the answer is quietly wrong.

The trap

Look at user 391 in the sample: three sessions of 1, 2, and 3 pages, total 6, a genuine low-engagement user. Now imagine a user with two 8-page sessions. If you filter pages_viewed BETWEEN 1 AND 9 in WHERE, both of those 8-page sessions pass the row filter and sum to 16, yet the user is not low-engagement at all. WHERE cannot see the total because the total does not exist until after aggregation. That is exactly what HAVING is for.

Wrong: filter in WHERE

SELECT user_id, SUM(pages_viewed) FROM user_sessions WHERE pages_viewed BETWEEN 1 AND 9 GROUP BY user_id Filters sessions before summing, so a user with many small sessions can still exceed 9 in total and slip through, while a single 12-page session vanishes before the sum.

Right: filter in HAVING

SELECT user_id, SUM(pages_viewed) FROM user_sessions GROUP BY user_id HAVING SUM(pages_viewed) BETWEEN 1 AND 9 Sums every session first, then keeps only users whose lifetime total sits in range. The predicate reads the aggregate, which is the quantity the question is actually about.

Building the answer

Step 1: Collapse to one row per user

GROUP BY user_id and SUM(pages_viewed). This is the grain the question cares about: a user, not a session. Naming that grain out loud before writing the clause is the habit interviewers reward.

Step 2: Filter on the aggregate with HAVING

HAVING SUM(pages_viewed) BETWEEN 1 AND 9 keeps only the low-engagement totals. BETWEEN is inclusive on both ends, so a user summing to exactly 9 stays in, matching the requirement.

Step 3: Return the total and order it

Project the total alongside user_id and ORDER BY total_pages_viewed ascending so the quietest users surface first. Add user_id as a second sort key so users tied on the total come out in a stable, deterministic order (lowest id first) rather than an arbitrary one. The output is a list you can hand straight to a re-engagement campaign.

SELECT user_id, SUM(pages_viewed) AS total_pages_viewed
FROM user_sessions
GROUP BY user_id
HAVING SUM(pages_viewed) BETWEEN 1 AND 9
ORDER BY total_pages_viewed

Common follow-up questions

• How would the result change if some sessions had a NULL `pages_viewed`? (Tests whether the candidate knows SUM ignores NULLs and what that implies for a user whose sessions are all NULL.)
• Instead of the list, the growth team wants just the count of these users. What changes? (Tests wrapping the grouped result in an outer COUNT(*) and recognizing why you cannot count and list in the same flat query.)
• How would you extend this to only flag users whose sessions all occurred before a cutoff date? (Tests combining a pre-aggregation WHERE on `session_start` with the post-aggregation HAVING on the total in one query.)