Targeted Ad Campaigns

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

Airbnb

Problem

Only clicked impressions belong in our audit, and only those served for the 'RETARGETING_CART' and 'BRAND_AWARENESS_Q1' campaigns. Pull those full impression records from ad_impressions.

High-value impressions. Targeted precisely.

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 boolean-precedence check wearing an ad-audit costume. Anyone can name the two campaigns and the click flag. What separates candidates is whether the click condition lands on both campaigns or only on the second one. Write ad_campaign = 'RETARGETING_CART' OR ad_campaign = 'BRAND_AWARENESS_Q1' AND clicked = 1 and SQL reads it as RETARGETING_CART OR (BRAND_AWARENESS_Q1 AND clicked = 1), because AND binds tighter than OR. Every unclicked 'RETARGETING_CART' impression slides into the audit, and on this table that is most of them. The click audit comes back padded with non-clicks.

Getting there

Step 1: Pin the campaigns as one set

Treat the two labels as a single membership test: ad_campaign IN ('RETARGETING_CART', 'BRAND_AWARENESS_Q1'). Precedence cannot split a list, so whatever you AND onto it applies to the whole set. Match the labels exactly, because 'PRODUCT_LAUNCH_X' and 'SUMMER_SALE_2024' sit right beside them in ad_impressions.

Step 2: Keep only the clicks

clicked is an INTEGER flag, 1 for a click and 0 otherwise, so the predicate is clicked = 1. It is ANDed after the membership test, which covers both campaigns and keeps clicks only.

Step 3: Return the whole record

The audit wants each impression as stored, so the projection is the full row of ad_impressions. Some impressions carry a NULL user_id. They are still impressions, and nothing in the ask drops them, so add no extra filter.

SELECT *
FROM ad_impressions
WHERE ad_campaign IN ('RETARGETING_CART', 'BRAND_AWARENESS_Q1') AND clicked = 1

SELECT *
FROM ad_impressions
WHERE ad_campaign = 'RETARGETING_CART'
   OR ad_campaign = 'BRAND_AWARENESS_Q1' AND clicked = 1

Bare OR

The click filter attaches only to 'BRAND_AWARENESS_Q1'. Every 'RETARGETING_CART' row returns, whatever its clicked value.

Grouped membership

ad_campaign IN (...) AND clicked = 1, or the same OR wrapped in parentheses. The click filter covers both campaigns and only clicked rows survive.

Common follow-up questions

• If `ad_campaign` arrived in mixed case, such as '`Retargeting_Cart`', how would you keep the filter robust? (Normalizing case inside the membership test without breaking exact matching.)
• Now restrict the audit to the last 90 days of `impression_time`: where does that predicate go, and does it need parentheses? (Whether the candidate reapplies the precedence lesson to a third condition.)
• Instead of raw rows, return each campaign's click count and total `revenue`. (Moving from a filter to a grouped summary over the same predicate.)