Unclicked Searches by Campaign

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

L3

Asked in

Apple

Problem

Our per-campaign search totals run higher than the search log itself, because a user shown the same campaign several times has their searches counted again for every impression. Searches sit in search_queries and campaign exposure in ad_impressions, connected only by the user behind each event: count each campaign's searches once apiece, broken down by the result position the searcher clicked, with searches that ended without a click as their own bucket. List the campaigns alphabetically and, within each, the no-click bucket first, then the clicked positions from lowest to highest.

Searched but never clicked.

Tables

search_queries

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

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

The metric hiding behind the join

This is a per-campaign search count wearing a campaign-attribution costume. A campaign never touches a search directly: the only bridge is user_id, so you join search_queries to ad_impressions on the user, then count searches per campaign and per clicked_result. The trap is what you count AFTER that join. Fire COUNT(*) and you count search-by-impression pairs, not searches: a user shown one campaign across three impressions has each of their searches counted three times. Every heavy user inflates their campaign's totals, and the position breakdown lands wrong for exactly the users who matter most.

Build it

Step 1: Attribute searches to campaigns

Join search_queries to ad_impressions on user_id. The shared user is the ONLY thing linking a search to a campaign, so this join is what attributes each search to the campaigns its user was shown. Searches with no user, or by a user never shown an ad, fall out here, and that is correct: they belong to no campaign.

Step 2: Count each search once

Group by ad_campaign and clicked_result, and count with COUNT(DISTINCT s.query_id). The DISTINCT is the whole game: the user_id join fans out, so the raw joined rows overcount searches. Counting unique query_id values collapses that fan-out back to one per real search.

Step 3: Keep the no-click bucket and put it first

A search that ended without a click carries a NULL in clicked_result, and it stays as its own group: never filter it away. Sort by ad_campaign, then clicked_result. An ascending sort here puts NULL first, which is where the no-click bucket belongs; on an engine that sorts nulls last you would add NULLS FIRST.

SELECT
    a.ad_campaign,
    s.clicked_result,
    COUNT(DISTINCT s.query_id) AS event_count
FROM search_queries s
JOIN ad_impressions a ON s.user_id = a.user_id
GROUP BY a.ad_campaign, s.clicked_result
ORDER BY a.ad_campaign, s.clicked_result

COUNT(*)

Counts joined rows: one search by a user shown a campaign in 3 impressions counts 3 times. The totals look busier than reality, and the position breakdown skews toward the heaviest users.

COUNT(DISTINCT `s.query_id`)

Counts each search once per campaign, no matter how many impressions carried it. This is the number the business actually asked for.

Common follow-up questions

• How would you report the click-through rate per campaign, not just raw counts? (Count unique `query_id` values where `clicked_result IS NOT NULL`, divided by the campaign's unique search count.)
• What if a search should only attribute to impressions within an hour of the query? (Add a time-window predicate to the join, correlating `query_time` with `impression_time` so a search attributes only to nearby impressions.)
• How would you pivot clicked versus unclicked into two columns per campaign? (Conditional aggregation over the deduped searches, testing `clicked_result` for `NULL` to fill the two columns.)