# Accounted For > Every impression is a face in the crowd; some we can name, the rest are strangers. Measure the gap, campaign by campaign. Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L3 ## Problem The attribution team is auditing the ad log campaign by campaign, where an impression counts as traceable only when it maps to a known user account. For each campaign, find the percentage of its impressions that are traceable, most traceable first. ## 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 the attribution team is measuring. Reach for an inner join and those rows vanish, every campaign reports a flawless 100 percent, and you have proudly measured nothing. > **Keep the misses, then count the hits** > > A LEFT JOIN from `ad_impressions` to `users` keeps every impression, filling the user columns with NULL when nothing matches. Now `u.user_id IS NOT NULL` is a clean flag: true only when a real account was found. Sum that flag for the numerator, count every row for the denominator, and the misses stay where they belong, in the total. --- ### 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: Divide, per campaign Group by `ad_campaign` so the flag-sum and the row-count collapse to one pair per campaign. Cast the numerator to REAL before multiplying by 100.0 so integer division does not floor the ratio, round to one decimal, and order most traceable first. --- ### The solution **Per-campaign attributable impression rate** ```sql SELECT ai.ad_campaign, ROUND(CAST(SUM(CASE WHEN u.user_id IS NOT NULL THEN 1 ELSE 0 END) AS REAL) * 100.0 / 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, ai.ad_campaign ``` > **Interviewers watch for** > > The tell is the join type. A candidate who writes LEFT JOIN without prompting has understood that the unmatched impressions are the signal, not noise to filter away. The second tell is the CAST: whoever forgets it and returns a column of zeros has never watched integer division floor a real ratio in SQL. > **Common pitfall** > > Counting the numerator with `COUNT(u.user_id)` instead of a CASE sum works here, but pairing it with `COUNT(ai.user_id)` for the denominator quietly drops null impression user_ids and shrinks the total. Use `COUNT(*)` so every impression counts once, matched or not. > **At scale** > > `ad_impressions` is 500M rows against a 5M-row dimension. The join key `user_id` carries 18 percent nulls, which never match and can be short-circuited; an index on `users.user_id` turns the lookup into a probe rather than a scan. Grouping by the 200-value `ad_campaign` keeps the aggregate's hash table tiny. ## 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.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/accounted_for) - [SQL Interview Questions](https://datadriven.io/sql-interview-questions) - [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep) - [Daily Challenge](https://datadriven.io/daily) --- Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.