Retargeting Campaign Impressions

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

Meta

Problem

Impression numbers for the retargeting segment don't reconcile with what the ad operations team expects. Pull every field from ad_impressions for the campaigns whose name contains 'retarget' so they can check what is actually flowing into that segment.

Retargeting impressions. All of them.

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

This is a case-mismatch filter dressed up as a reconciliation audit. You are handed the lowercase token 'retarget', but every stored value is the uppercase 'RETARGETING_CART'. Anyone can write SELECT * with a wildcard pattern. What separates candidates is noticing that the pattern, typed as given, is case-sensitive on the grader. Miss that and the audit comes back empty, which tells ad ops there is no retargeting traffic at all: a false answer that looks like a finding.

Getting to the query

Step 1: Read the stored values before writing the pattern

The sample rows show ad_campaign holding values like 'RETARGETING_CART' and 'BRAND_AWARENESS_Q1', all uppercase with underscores. The word you were given is lowercase. That gap is the whole problem, and you can see it in ten seconds of looking at the data.

Step 2: Match anywhere in the name, ignoring case

'retarget' sits at the start of 'RETARGETING_CART', but the ask is 'contains', so wrap it in % on both sides. Then make the comparison ignore case with ILIKE. Writing LIKE '%RETARGET%' in the stored case also works on this data; ILIKE keeps working if someone later ships 'Retargeting_Email'.

Step 3: Return the whole row

"Every field" means SELECT *. In an audit you don't yet know which column explains the discrepancy, so revenue (mostly NULL here because most impressions were never clicked), user_id and impression_time all have to come back.

Case-sensitive match

ad_campaign LIKE '%retarget%' compares letter by letter, 'r' is not 'R', and zero rows come back. It reads as correct and fails silently.

Case-insensitive match

ad_campaign ILIKE '%retarget%' folds case before comparing, so every 'RETARGETING_CART' impression is returned, from all years in the table.

SELECT *
FROM ad_impressions
WHERE ad_campaign ILIKE '%retarget%'

Common follow-up questions

• How would you return the matching impressions for only the last 30 days without scanning every partition? (Tests pruning on `impression_time`, the partition key.)
• What if a campaign named '`NON_RETARGETED_TEST`' appears and must be excluded? (Tests tightening a substring match with token boundaries or an explicit list.)
• How would you show retargeting impressions and clicks per month next to the totals ad ops reports? (Moves from row audit to reconciliation with a conditional count.)