The Widest Net

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

L4

Asked in

Travelport

Problem

Find each campaign's true reach from ad_impressions: how many different registered users clicked at least one of its ads, counting a person once however many times they clicked. The analyst building the campaign scorecard reads it widest-reaching first.

Not the clicks. The crowd: how many people each campaign actually pulled in.

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 asks

This is a count of different people dressed up as a campaign scorecard. Anyone can group ad_impressions by ad_campaign. What separates candidates is where the click condition and the per-person collapse land. Reach is how many different users clicked, so two things have to happen to user_id inside the same count: unclicked impressions drop out, and a person who clicked ten times counts once. Count click rows and LOYALTY_PROGRAM reports 71 instead of 13. Count different users without the click condition and you have measured who saw the ads, not who acted on them.

Counting click events

COUNT(*) or SUM(clicked) over clicked rows counts events. On this data LOYALTY_PROGRAM has 71 clicked impressions because the same monthly clickers come back year after year, so the campaign looks more than five times wider than it is.

Counting different clickers

COUNT(DISTINCT ...) over the clicking user_id collapses each person's repeat clicks. Those 71 rows come from 13 different users (one click carries no user at all), so reach is 13.

Building it

Step 1: Make the click condition part of the count

Group ad_impressions by ad_campaign and count CASE WHEN clicked = 1 THEN user_id END. A clicked row hands its user_id to the count; an unclicked row hands it NULL, which the count skips. Drop the CASE and the same query counts every viewer: exposure, not reach.

Step 2: Collapse repeat clickers with DISTINCT

COUNT(DISTINCT ...) keeps one entry per person per campaign. SUMMER_SALE_2024 has 7 clicked impressions from 6 people, and the gap is far wider wherever a regular keeps clicking. Anonymous clicks with a NULL user_id fall out for free, which is what 'registered users' asks for.

Step 3: Put the widest reach first

Sort by users_reached descending. Six campaigns tie at one clicker each. Nothing in the ask orders them against each other, so any order among them is correct.

The solution

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

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 table.)
• 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 'different user' really means when the keys are imperfect.)