Ad Revenue by Age Bucket

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

Asked in

Airbnb

Problem

How much ad revenue does each age bucket bring in, with the impressions' revenue in ad_impressions and each user's bucket in users, from the highest-earning bucket down? Users with no age bucket on file stay out of the totals.

Ad dollars, sliced by country.

Tables

ad_impressions

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

users

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

Expected output

Worked solution and explanation

What this is really asking

This is a fact-to-dimension rollup in a demographics costume. The money lives in ad_impressions.revenue and the age bucket lives in users.age_bucket, so neither table can answer the question alone. Everyone remembers to sum. What separates candidates is joining before grouping and sorting on the aggregate rather than the label. Group ad_impressions by user_id and you return per-user totals that never roll up to a demographic. Sort by age_bucket and '18-24' leads only because it comes first as text.

Build it in three moves

Step 1: Join ad_impressions to users on user_id

The bucket exists only on the user row, so every impression has to borrow it. Inner is the right join here: the impressions with a NULL user_id have no bucket to land in, and an inner join drops them without a special case.

Step 2: Drop users with no age_bucket before grouping

Some users have no bucket on file, and several of them hold impressions. Without WHERE u.age_bucket IS NOT NULL, their revenue forms its own blank group, a seventh row nobody asked for. Filtering in WHERE means that group is never built.

Step 3: Sum revenue per bucket and sort on the total

SUM(ai.revenue) skips the roughly half of impressions that earned nothing, so no COALESCE is needed. Sort on total_revenue descending. That value exists only after the aggregate, which is exactly why the sort belongs on it.

SELECT u.age_bucket,
       SUM(ai.revenue) AS total_revenue
FROM ad_impressions ai
INNER JOIN users u
  ON ai.user_id = u.user_id
WHERE u.age_bucket IS NOT NULL
GROUP BY u.age_bucket
ORDER BY total_revenue DESC

Single-table shortcut

SELECT user_id, SUM(revenue) FROM ad_impressions GROUP BY user_id runs cleanly and returns a couple dozen per-user totals. It looks like progress, but age_bucket can never be reached from here.

Join, then roll up

Joining to users first gives every impression a bucket, so GROUP BY u.age_bucket collapses everything into six demographic rows that read top to bottom.

Common follow-up questions

Common follow-up questions

• How would you split this into clicked and unclicked revenue per bucket? (Add `SUM(CASE WHEN clicked = 1 THEN revenue END)` next to the total to show conversion lift per cohort.)
• What if `age_bucket` lived on a slowly changing dimension instead of `users`? (You would join on `user_id` plus a valid-from range against `impression_time`, which turns this into an inequality join.)
• How would you cap the result to the top three buckets only? (Apply a limit of three after sorting, or use a rank if every bucket tied on revenue should be returned.)