User 360

A hard SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

hard

Seniority

L6

Problem

Each user in users can have many purchases in transactions and many visits in user_sessions. Produce one row per user, including users with no activity at all, with their number of transactions, total amount spent and total time on site in seconds, showing 0 wherever there is nothing to count. Next to every user goes the platform-wide average of total spend across the users who have transacted.

One row per user. Everything they did, or didn't do.

Tables

users

Columns: user_id INTEGER, username TEXT, email TEXT, signup_date TEXT, account_status TEXT, age_bucket TEXT

transactions

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

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 really is

Strip the activity-summary framing and this is two independent one-to-many rollups hung off the same parent, plus one population-level benchmark. Both transactions and user_sessions carry many rows per user, so the tempting move is one flat query that LEFT JOINs users to both and sums. That query quietly pairs every one of a user's transactions with every one of their sessions before the SUM ever runs: total_spent comes back multiplied by the session count and total_session_seconds by the transaction count. And no, SUM(DISTINCT ...) does not save you, because amounts and durations repeat within a user, so de-duping throws away real rows. The only correct path is to collapse each table to one row per user first, then join, and to fetch the platform average with a scalar subquery, because a per-row view of the whole population cannot come out of the same grouping.

Build it

Step 1: Pre-aggregate transactions

SELECT user_id, COUNT(*) AS transaction_count, SUM(total_amount) AS total_spent FROM transactions GROUP BY user_id collapses the transaction rows to one row per user. Crucially the COUNT and SUM happen here, before any join, so each transaction is seen exactly once.

Step 2: Pre-aggregate sessions

SELECT user_id, SUM(session_duration_sec) AS total_session_seconds FROM user_sessions GROUP BY user_id does the same for sessions, one row per user with the true total time on site.

Step 3: Left join from users to both aggregates

Drive off users and LEFT JOIN both aggregates on user_id. LEFT JOIN keeps users with no transactions or no sessions; COALESCE turns the resulting NULLs into 0 so every user, including the ones who never acted, reads as a real number.

Step 4: Attach the population benchmark

The benchmark is one number for the whole result, so it belongs in a scalar subquery: (SELECT AVG(user_total) FROM (SELECT SUM(total_amount) AS user_total FROM transactions GROUP BY user_id)). Total each transacting user's spend first, then average those totals. It is NOT AVG(total_amount) over raw transactions, which averages purchases, not people.

The solution

SELECT
    u.username,
    COALESCE(t.transaction_count, 0) AS transaction_count,
    COALESCE(t.total_spent, 0) AS total_spent,
    COALESCE(s.total_session_seconds, 0) AS total_session_seconds,
    (
        SELECT AVG(user_total)
        FROM (
            SELECT SUM(total_amount) AS user_total
            FROM transactions
            GROUP BY user_id
        )
    ) AS avg_spend_per_active_user
FROM users u
LEFT JOIN (
    SELECT user_id, COUNT(*) AS transaction_count, SUM(total_amount) AS total_spent
    FROM transactions
    GROUP BY user_id
) t ON u.user_id = t.user_id
LEFT JOIN (
    SELECT user_id, SUM(session_duration_sec) AS total_session_seconds
    FROM user_sessions
    GROUP BY user_id
) s ON u.user_id = s.user_id

Common follow-up questions

• How would you add each user's average order value to this summary? (Add `AVG(total_amount)` to the transactions subquery; it composes cleanly because the grain is already one row per user.)
• How would you flag users whose spend is above the platform average? (Turn the benchmark into a comparison flag with a CASE expression over the same scalar subquery, or subtract it to show each user's distance from the mean.)
• How would you serve this for a dashboard at petabyte scale? (Pre-compute the per-user rollups into summary tables or materialized views refreshed on a schedule, so the dashboard reads a small table instead of scanning 140M rows at query time.)
• What if `user_id` were stored as TEXT in one table and INTEGER in another? (Cast the join keys to a common type in the ON clause, or fix the upstream schema; a silent type mismatch would make every join miss and return all zeros.)