# The Widest Net > Not the clicks. The crowd: how many people each campaign actually pulled in. Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L4 ## Problem The ad analytics team wants each campaign's true reach: how many different registered users clicked at least one of its ads, counting a person once however many times they clicked. Keep campaigns that drew no clicks at all, and list the widest-reaching first. ## Worked solution and explanation ### What this really asks This is a count of distinct people wearing a marketing costume. Reach is 'how many different users did this campaign pull a click from,' and the whole game is counting each clicking user once per campaign, not counting click events. Two traps sit in plain sight. Count impression rows and one chatty user who clicked five times makes the campaign look five users wide. Push clicked = 1 into a WHERE clause and every campaign that got impressions but no clicks silently drops out, so 'the widest net' quietly loses the campaigns whose reach is actually zero. **COUNT(*) or SUM(clicked): counts events** These count clicks, not people. In the sample, user 100 clicked LOYALTY_PROGRAM twice, so COUNT(*) reports 4 click rows for that campaign. Repeat clicks from one person inflate reach and reward noisy users instead of wide ones. **COUNT(DISTINCT user_id): counts people** Collapse a user's repeat clicks so each person counts once. LOYALTY_PROGRAM's four click rows come from three distinct users (100, 391, 682), so reach is 3. That is the number the business means by 'how many users did we reach.' ### Building it #### Step 1: Count distinct clickers per campaign Group ad_impressions by ad_campaign and take COUNT(DISTINCT CASE WHEN clicked = 1 THEN user_id END). The CASE keeps only rows where the user actually clicked, and DISTINCT collapses a user's repeat clicks so they count once. Non-clicked rows fall through the CASE as NULL, which COUNT(DISTINCT ...) ignores. #### Step 2: Keep the campaigns nobody clicked The filter for clicked = 1 lives inside the CASE, not in a WHERE clause. Because grouping still runs over every impression, a campaign whose users never clicked still forms a group and its conditional count simply comes out 0. Move that filter to WHERE and those campaigns disappear before grouping ever sees them. #### Step 3: Order the widest net first Sort by the reach count descending so the campaigns that touched the most users lead, then by ad_campaign ascending so campaigns tied on reach fall in a stable, predictable order. --- ### The solution **Reach as distinct clicking users per campaign** ```sql SELECT ai.ad_campaign, COUNT(DISTINCT CASE WHEN ai.clicked = 1 THEN ai.user_id END) AS users_reached FROM ad_impressions ai GROUP BY ai.ad_campaign ORDER BY users_reached DESC, ai.ad_campaign ASC ``` > **Trick to solving** > > Two moves crack it: count DISTINCT users rather than rows, and keep the clicked filter inside the CASE so zero-click campaigns survive grouping at 0. Miss either one and the numbers look plausible but are wrong. > **Common pitfall** > > Filtering clicked = 1 in the WHERE clause looks harmless and is the classic wrong turn: it removes every impression from zero-click campaigns, so those campaigns never form a group and vanish from the result. Keep the filter inside the CASE so the group survives at reach 0. > **Interviewers watch for** > > The tell is reaching for COUNT(DISTINCT user_id) instead of COUNT(*) or SUM(clicked). The first counts people; the other two count events. A candidate who says out loud that repeat clicks from one user should not widen reach has already shown they understand the grain. > **Performance insight** > > ad_impressions is 250M rows across 365 daily partitions, but there are only 200 distinct campaigns. This is a single grouped scan of the fact table with a distinct count per group, and the result is at most 200 rows. No join to the 15M-row users table is needed, since reach is entirely derivable from the impressions the campaigns generated. --- ## Common follow-up questions - The team now wants reach limited to the last 30 days. Where does that date filter go, and can it live in WHERE without dropping zero-click campaigns? _(Tests when a WHERE filter is safe (it narrows the fact scan) versus when it silently removes whole groups.)_ - How would you also express each campaign's reach as a percentage of the total registered user base, in the same query? _(Tests bringing in a global denominator via a scalar subquery over users without fanning the big join.)_ - If one person clicked the same campaign from two device rows carrying different user_ids, how would you avoid counting them twice? _(Tests identity resolution and what 'distinct user' really means when the keys are imperfect.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/the_widest_net) - [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.