# Even the Silent Ones > The quietest accounts still belong in the numbers. Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L5 ## Problem We're building a single per-user report that pairs how often each account shows up with how much it has spent. Every user belongs on it, including the ones that never started a session or never made a purchase. ## Worked solution and explanation ### What this problem really is Strip the business costume and this is a per-user set of independent rollups bolted onto a full roster: count each account's sessions, sum each account's spend, and keep every account whether it has any activity or not. Anyone can write the joins. The trap is that sessions and transactions are two separate one-to-many branches hanging off the same `user_id`, and if you join them together before you aggregate, they multiply each other. A user with 10 sessions and 20 transactions becomes 200 rows: the session count reads 200 and the revenue is inflated tenfold. Miss the outer join and every silent account disappears from the report entirely. > **Trick to solving** > > The words "every user still appears" are the outer-join tell, but the real move is to collapse each child table to one row per user FIRST, then attach it. Check whether two child tables share the same parent key: if they do, joining them raw is a fan-out waiting to happen. > > 1. Recognize the two one-to-many relationships converging on `user_id` > 2. Pre-aggregate each child table into its own CTE before joining > 3. LEFT JOIN from users to each pre-aggregated CTE > 4. COALESCE the unmatched NULLs to 0 --- ### Build it up #### Step 1: Collapse sessions to one row per user In a CTE, `SELECT user_id, COUNT(*) AS session_count FROM user_sessions GROUP BY user_id` reduces the session rows to one row per user. That single row is what makes the later join safe: there is nothing left to fan out against. #### Step 2: Collapse transactions to one row per user A second CTE, `SELECT user_id, SUM(total_amount) AS total_amount FROM transactions GROUP BY user_id`, does the same for spend. Note it is SUM, not COUNT: the ask is total revenue, and counting transaction rows is a common slip. #### Step 3: Anchor on the user roster with LEFT JOIN `FROM users u LEFT JOIN session_agg sa ON u.user_id = sa.user_id LEFT JOIN txn_agg ta ON u.user_id = ta.user_id` anchors on the full user roster so an account with zero sessions or zero transactions still comes through. Swap either LEFT for an inner join and those accounts vanish. #### Step 4: Turn the misses into zeros A user who matched neither CTE arrives with NULLs. `COALESCE(session_count, 0)` and `COALESCE(total_amount, 0)` turn those into the zeros the report is supposed to read as no activity. --- ### The solution **Pre-aggregate, then LEFT JOIN to avoid fan-out** ```sql WITH session_agg AS ( SELECT user_id, COUNT(*) AS session_count FROM user_sessions GROUP BY user_id ), txn_agg AS ( SELECT user_id, SUM(total_amount) AS total_amount FROM transactions GROUP BY user_id ) SELECT u.username, COALESCE(sa.session_count, 0) AS session_count, COALESCE(ta.total_amount, 0) AS total_amount FROM users u LEFT JOIN session_agg sa ON u.user_id = sa.user_id LEFT JOIN txn_agg ta ON u.user_id = ta.user_id ``` > **Why this stays cheap at scale** > > Each CTE scans its child table exactly once and collapses it: the 30,000,000 session rows and 60,000,000 transaction rows shrink to at most one grouped row per user before anything joins. The final step is two hash joins against the 5,000,000-row roster. Because the aggregation happens first, the fan-out product is never materialized and peak memory stays bounded by the grouped sets, not by their cross product. > **Interviewers watch for** > > The first thing an interviewer looks for is whether you spot the fan-out before writing anything. A candidate who reaches for `users JOIN sessions JOIN transactions` and aggregates at the end produces inflated numbers and usually does not notice. Pre-aggregating each branch in its own CTE is the tell of someone who has been burned by this before. > **Common pitfall** > > Joining sessions and transactions straight onto users without collapsing them first is a Cartesian product per user: 10 sessions times 20 transactions is 200 rows. The COUNT then reports 200 and every summed amount is multiplied by the session count. The result looks plausible, which is exactly why it slips through. --- ## Common follow-up questions - Now they only want accounts that both logged a session and made a purchase. What changes? _(Tests whether the candidate understands inner vs left join semantics and can switch the anchor deliberately.)_ - How would you add average transaction value per user without scanning transactions a second time? _(Tests whether the candidate can add a second metric inside an existing CTE rather than re-scanning the table.)_ - Both child tables are huge. Why pre-aggregate in CTEs instead of joining everything and grouping at the end? _(Tests the core fan-out reasoning: why aggregate before joining rather than join then group.)_ - This report needs to refresh hourly over 60M+ transactions. How would you keep it from re-scanning everything each run? _(Tests awareness of incremental refresh and materialization at production scale.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/even_the_silent_ones) - [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.