Accounted For

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

Rockerbox

Problem

Our per-campaign attribution numbers look too good, and we suspect many ad impressions can't be tied back to a real account at all. For each campaign in ad_impressions, give the share of its impressions that belong to a known account in users, as a decimal percentage rounded to one place, most traceable first.

Every impression is a face in the crowd; some we can name, the rest are strangers. Measure the gap, campaign by campaign.

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

Strip off the marketing vocabulary and this is a per-group match rate: for each campaign, what fraction of its impressions find a partner row in users? The whole problem lives in one decision, whether an impression with no matching account survives to the denominator. It has to. An impression with a null user_id, or a user_id that points at no real account, is exactly the incompleteness being measured. Reach for an inner join and those rows vanish, every campaign reports a flawless 100 percent, and you have proudly measured nothing.

Build it

Step 1: LEFT JOIN, not INNER

Join ad_impressions to users on user_id, keeping the impression table on the left. Impressions whose user_id is null, or whose user_id has no row in users, are retained with NULL user columns instead of being dropped. Those surviving rows are the point of the exercise.

Step 2: Flag the traceable ones

SUM(CASE WHEN u.user_id IS NOT NULL THEN 1 ELSE 0 END) counts only impressions that landed on a real account. Both failure modes, a null impression user_id and a user_id absent from users, leave u.user_id NULL after the join, so one condition catches both.

Step 3: Cast, divide, per campaign

Group by ad_campaign so the flag-sum and the row-count collapse to one pair per campaign. Both are integers, so cast the numerator to REAL before the arithmetic: without it, 16 * 100 / 21 floors to 76 and a 76.2 percent campaign reports 76.0. Round to one decimal and sort most traceable first; campaigns tied at 100.0 may come in either order.

The solution

SELECT ai.ad_campaign,
       ROUND(CAST(SUM(CASE WHEN u.user_id IS NOT NULL THEN 1 ELSE 0 END) AS REAL) * 100 / COUNT(*), 1) AS attributable_pct
FROM ad_impressions ai
LEFT JOIN users u ON ai.user_id = u.user_id
GROUP BY ai.ad_campaign
ORDER BY attributable_pct DESC

Common follow-up questions

• If `users` held more than one row per user, say one per account status, how would the fan-out change your numerator and denominator? (Tests whether the candidate sees join fan-out inflating both counts when the dimension is not unique on the join key.)
• The team now wants only campaigns where fewer than 80 percent of impressions are traceable. Where does that filter go? (Tests placement of an aggregate filter: HAVING on the computed rate rather than WHERE.)
• Alongside the percentage, they also want the raw count of untraceable impressions per campaign. How do you add it in one pass? (Tests composing several conditional aggregates in a single scan.)