# On Their Way Out > They signed up. They never really showed up. Canonical URL: Domain: SQL · Difficulty: easy · Seniority: L4 ## 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. ## 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 it ascending so the quietest users surface first. The output is a list you can hand straight to a re-engagement campaign. **Per-user total with an inclusive range filter** ```sql 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 ``` > **Interviewers watch for** > > The instant you say the filter belongs on the summed total rather than on each session, you have shown you understand aggregation ordering. Candidates who reach for WHERE and never notice the difference read as syntax-first; naming the grain and the post-aggregation filter reads as senior. > **Common pitfall** > > Filtering pages_viewed in WHERE instead of the summed total in HAVING. WHERE runs before grouping and sees only raw session values; HAVING runs after and sees the SUM. On this problem the two produce different, non-obvious answers, so it is a silent correctness bug, not an error. > **At 60M sessions** > > The aggregation is the whole cost: a single scan of user_sessions hashed by user_id, collapsing 60M rows to roughly 4M user totals before HAVING trims them. There is no join and no self-reference, so the plan stays one grouped scan; an index on user_id helps only if the engine can aggregate along it. --- ## 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.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/on_their_way_out) - [SQL Interview Questions](https://datadriven.io/sql-interview-questions) - [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep) - [Daily Challenge](https://datadriven.io/daily) --- Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.