Who We Reached

An easy SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

easy

Seniority

L3

Asked in

Natera

Problem

Our reach view folds every year of the ad_impressions log into its calendar month, so a March impression from one year and a March impression from another land in the same month. For each ad campaign and month, find how many different users saw an impression, campaigns in order and each campaign's months from earliest to latest.

Monthly reach, campaign by campaign.

Tables

ad_impressions

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

Expected output

Worked solution and explanation

What this is really asking

This is a people-versus-events question wearing a calendar costume. Anyone can pull a month out of impression_time and group by campaign. What separates candidates is reading 'different users' as COUNT(DISTINCT user_id) and reading 'calendar month' as one bucket per month number across every year in the log. Count rows instead and every impression with no recorded viewer still adds reach, so a campaign-month made only of those reports a viewer that does not exist. Bucket by year and month instead and March splits into a row per year, which is a different report.

Break down the requirements

Step 1: Pick the grain: campaign and month number

The output is one row per ad_campaign per calendar month. The log spans several years, and the statement says they fold together, so the year never enters the key. Decide this before writing anything: the grain is the whole answer's shape.

Step 2: Turn the text timestamp into a number

impression_time is TEXT, so STRFTIME('%m', impression_time) hands back '03', a string. Wrapping it in CAST(... AS INTEGER) makes it 3, the numeric month the expected output shows. Name it month once and group and sort by that name, so the expression lives in one place.

Step 3: Count people, not rows

COUNT(DISTINCT user_id) tallies each viewer once per group no matter how often they saw the ad, and it skips NULLs, so impressions with no recorded viewer add nothing. A group made only of those reports 0, as 'HOLIDAY_PROMO' in month 1 does.

Step 4: Order the output

Sort by ad_campaign, then month. The two keys are unique per row, so the order is total and no tiebreaker is needed.

The solution

SELECT ad_campaign,
    CAST(STRFTIME('%m', impression_time) AS INTEGER) AS month,
    COUNT(DISTINCT user_id) AS unique_users
FROM ad_impressions
GROUP BY ad_campaign, month
ORDER BY ad_campaign, month

Common follow-up questions

• If the same user saw a campaign 50 times in one month, how does `COUNT(DISTINCT user_id)` handle it, and what would change if you dropped `DISTINCT`? (Tests distinct counting versus raw counting.)
• Some impressions have a NULL `user_id`. How does `COUNT(DISTINCT user_id)` treat them, and is that what you want for a reach metric? (Tests NULL handling in aggregates.)
• The team now wants each year's months as separate rows. What changes in the query and in the row count? (Tests choosing the time grain deliberately.)
• At billions of rows, would you accept an approximate unique count here, and how would you explain the error bars to the growth team? (Tests the exact versus approximate distinct trade-off at scale.)