Even the Silent Ones

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

L5

Asked in

Citizens

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.

The quietest accounts still belong in the numbers.

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

transactions

Columns: transaction_id INTEGER, user_id INTEGER, total_amount REAL, product_id INTEGER, quantity INTEGER, transaction_date TEXT

Expected output

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.

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. It is a SUM, not a COUNT: the ask is how much each account spent, 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 JOIN for an inner join and those accounts vanish: on this data that is most of the roster.

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

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

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.)