Average Compensation by Department and Status

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

L3

Problem

Finance is comparing how much value different kinds of accounts bring in. For each account status, find the average total value per user, where a user's value is everything they spent on purchases plus the ad revenue their impressions earned. Users who never purchased or never earned ad revenue still count, contributing zero from whichever side they lack.

Average compensation. Department by department.

Tables

users

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

cost_allocs

Columns: alloc_id INTEGER, team_name TEXT, svc_name TEXT, category TEXT, amount REAL, period TEXT, region TEXT, acct_id TEXT

transactions

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

ad_impressions

Columns: impression_id INTEGER, user_id INTEGER, ad_campaign TEXT, impression_time TEXT, clicked INTEGER, revenue REAL

Expected output

Worked solution and explanation

What this really is

Underneath the finance framing this is a per-user rollup of two sparse fact tables, stitched back onto the full user list and made null-safe before anything is averaged. Each user's value is their spend in transactions plus their earnings in ad_impressions, and the whole difficulty is that most users are missing one side or the other: only a handful ever transacted, and most impressions carry no revenue at all. Inner-join the facts and you quietly drop every user who never bought or never earned ad revenue. Add a raw NULL sum and that user's total collapses to NULL and vanishes from the AVG. Either slip averages only your most active users, so every account status looks far richer than it is.

The trap

Inner join, raw sums

Inner-join the two fact sides and add them directly. A user with no ad revenue never appears, and a user with an impression but NULL revenue nulls their whole total. Both silently leave the average.

Outer join, coalesce to 0

Keep every user by anchoring on users, take spend as a per-user sum and revenue through a LEFT JOIN, then COALESCE(..., 0) each side. A missing side contributes 0, the user stays in, and the AVG runs over the true population.

Building it

Step 1: Total each user's spend as a per-user sum

Compute each user's spend with a scalar sum over transactions scoped to that user: (SELECT SUM(t.total_amount) FROM transactions t WHERE t.user_id = u.user_id). The WHERE t.user_id = u.user_id correlates the sum to the one user the outer row is about, so a user with several purchases gets a single correct total with no fan-out, and a user with none returns NULL rather than disappearing.

Step 2: Attach ad revenue without dropping anyone

Pre-aggregate ad_impressions to one row per user (SUM(revenue) ... GROUP BY user_id) and LEFT JOIN that rollup onto the user set. The LEFT JOIN is load-bearing: an inner join would drop every user with no ad revenue, which is most of them, and pull each status average upward.

Step 3: Neutralize the nulls, then average by status

COALESCE(..., 0) each side before adding, so a missing spend or a missing revenue contributes 0 instead of nulling the whole total and evicting the user from the AVG. Anchor the query on users for the full population and its account_status, then group by status and average the per-user totals.

SELECT u2.account_status,
       AVG(
         COALESCE((SELECT SUM(t.total_amount)
                   FROM transactions t
                   WHERE t.user_id = u.user_id), 0)
         + COALESCE(a.total_rev, 0)
       ) AS avg_total_value
FROM users u
JOIN users u2 ON u2.user_id = u.user_id
LEFT JOIN (
  SELECT user_id, SUM(revenue) AS total_rev
  FROM ad_impressions
  GROUP BY user_id
) a ON a.user_id = u.user_id
GROUP BY u2.account_status

Common follow-up questions

• How would the query change if you wanted the average only over users who both purchased and earned ad revenue? (Tests whether they see the outer join and the coalesce as a deliberate choice, reversible to an inner join.)
• A user has 500 impressions but only 2 carry `revenue`. Does your per-user total still compute correctly? (Probes the per-user rollup grain and the NULL-in-SUM behavior.)
• How would you extend this to break the average out by `age_bucket` as well as `account_status`? (Adds a second grouping dimension on top of the same rollup.)