# Who We Reached > Monthly reach, campaign by campaign. Canonical URL: Domain: SQL · Difficulty: easy · Seniority: L3 ## Problem The growth team is tracking how each ad campaign's reach moves month over month, where every impression falls within a single year. For each campaign and calendar month, find how many different users saw an impression, sorted by campaign and then by month. ## Worked solution and explanation ### What this is really asking This is a metric-definition problem wearing a SQL costume: do you count users or impressions? Anyone can group by campaign and pull a month out of the timestamp. The separator is COUNT(DISTINCT user_id). A heavy user who saw the same campaign ten times in March is one person, not ten. Drop the DISTINCT and every reach number inflates by the impressions-per-user fan-out, which on this data averages about 20x, so a campaign with 50,000 real viewers reports a million. --- ### Break down the requirements #### Step 1: Aggregate distinct users at the right grain The output grain is one row per (campaign, month). Pull the calendar month out of impression_time, group by both keys, and wrap user_id in COUNT(DISTINCT user_id) so each user is tallied once per group no matter how many impressions they generated. impression_time is stored as TEXT, so extract the month with STRFTIME and CAST it to an integer to match the expected numeric month column. #### Step 2: Order the final output Sort by ad_campaign and then by month so the output is deterministic and matches the expected sequence. The two grouping keys together are unique per row, so no extra tie-break column is needed here. --- ### The solution **Campaign-month distinct user count** ```sql 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, CAST(STRFTIME('%m', impression_time) AS INTEGER) ORDER BY ad_campaign, month ``` > **Trick to solving** > > The difference between right and wrong here is one keyword. Group by (campaign, month, user_id) and you have counted impressions; keep the grain at (campaign, month) and put DISTINCT inside the COUNT and you have counted people. Say 'unique users means a people count, so DISTINCT on user_id' out loud and you have already shown you read the metric correctly. > **Cost analysis** > > The query scans all 300M rows once and collapses them to one row per (campaign, month): roughly 200 campaigns times 12 months, a few thousand output rows. COUNT(DISTINCT user_id) is the costly part, since the engine tracks the distinct user set per group, with memory proportional to the largest group. A composite index on (ad_campaign, impression_time, user_id) lets the engine walk groups in order and bound that memory. > **Interviewers watch for** > > Naming the output grain ('one row per campaign per month') before writing GROUP BY shows you think in data shape, not syntax. The senior tell is reaching for COUNT(DISTINCT user_id) unprompted, because it proves you read 'unique users' as a count of people rather than a count of events. > **Common pitfall** > > impression_time is TEXT. Comparing or sorting it without extracting the month gives lexicographic, not chronological, behavior, and forgetting the CAST around STRFTIME leaves the month as a string that may sort and join oddly. Always confirm the column type before treating it as a date. --- ## 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 understanding of distinct counting versus raw counting.)_ - Some impressions have a NULL user_id. How does COUNT(DISTINCT user_id) treat them, and is that the behavior you want for a reach metric? _(Tests NULL handling, since the data contains rows with a NULL user_id.)_ - What index on ad_impressions would you create to speed up the grouping on (ad_campaign, impression_time)? _(Tests indexing knowledge for the grouping columns that actually drive this query.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/who_we_reached) - [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.