Impressions by Search Keyword

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

Domain

SQL

Difficulty

hard

Seniority

L3

Asked in

Meta

Problem

Someone who ran several searches in search_queries is still one member of the audience, so each impression they received in ad_impressions counts once, not once per search. Leaving out the time-boxed sale campaigns (any ad_campaign with 'SALE' in its name), ad sales wants every other campaign's impressions that reached a searcher, from most impressions to least.

Campaign performance, keyword by keyword.

Tables

ad_impressions

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

search_queries

Columns: query_id INTEGER, user_id INTEGER, search_term TEXT, results_count INTEGER, clicked_result INTEGER, query_time TEXT

Expected output

Worked solution and explanation

What this really is

This is a fan-out join dressed up as a campaign-reach report. Anyone can join search_queries to ad_impressions on user_id and count by campaign. What separates candidates is noticing that search_queries holds one row per search, not one per person. Each search row re-matches every impression that user ever saw, so a user with two searches counts every ad twice. Miss it and every campaign's reach is inflated, quietly, with numbers that still look plausible.

Build it in order

Step 1: Collapse searches to searchers

SELECT DISTINCT user_id FROM search_queries turns searches into searchers. The statement says a person is one audience member, so this is the grain the join needs. Doing it first keeps the join input small and the count honest.

Step 2: Join the searchers to their impressions

Inner-join searchers to ad_impressions on user_id. Impressions with a null user_id match nobody and drop out; a campaign that reached no searcher never appears.

Step 3: Drop the sale campaigns

ad_campaign NOT LIKE '%SALE%' removes 'FLASH_SALE_48H' and 'SUMMER_SALE_2024'. The word sits in the middle of one name and the end of the other, so both wildcards matter.

Step 4: Count per campaign, biggest first

GROUP BY ad_campaign, count the rows as impression_count, and sort by it descending. That is the whole order the statement asks for; campaigns tied on the count may come back either way.

SELECT ai.ad_campaign, COUNT(*) AS impression_count
FROM (
  SELECT DISTINCT user_id
  FROM search_queries
) searchers
INNER JOIN ad_impressions ai ON ai.user_id = searchers.user_id
WHERE ai.ad_campaign NOT LIKE '%SALE%'
GROUP BY ai.ad_campaign
ORDER BY impression_count DESC;

SELECT ai.ad_campaign, COUNT(*) AS impression_count
FROM search_queries sq
INNER JOIN ad_impressions ai ON ai.user_id = sq.user_id
WHERE ai.ad_campaign NOT LIKE '%SALE%'
GROUP BY ai.ad_campaign
ORDER BY impression_count DESC;

Dedupe after the join

COUNT(DISTINCT ai.impression_id) over the raw join lands on the same numbers, but the engine first materializes every fanned-out row and only then throws the duplicates away.

Dedupe before the join

DISTINCT user_id removes the repeats on the small side, so the join never produces them and a plain COUNT(*) is correct.

Common follow-up questions

• If only impressions within a day after a search should count, what happens to the `DISTINCT user_id` step? (Tests attribution-window thinking: each search has its own `query_time`, so collapsing to searchers first is no longer valid.)
• If sales wanted the same report broken down by `search_term`, what would you dedupe on, and what would `search_term` need first? (Tests whether the dedupe grain follows the report grain.)
• How would you report the number of unique searchers each campaign reached instead of impressions? (Tests reach versus frequency.)