Holiday Promo Campaign Click Year

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

Uber

Problem

List the months in which the HOLIDAY_PROMO campaign drew at least one click. The ad_impressions log spans several years, so a month here is a specific year and month, never the same calendar month pooled across years.

One year, the holiday campaign exploded.

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

What this is really asking

This is a click count wearing an impression table's clothes. Every row of ad_impressions is one impression served, and a click is just clicked = 1 on that row. Anyone can filter to 'HOLIDAY_PROMO' and bucket by month. What separates candidates is the threshold: HAVING COUNT(*) >= 1 is true for every month the campaign was shown at all, so it returns all 11 served months instead of the 3 that drew a click. `SUM(clicked)` is the click count; COUNT(*) is the impression count.

Step 1: Filter the campaign early

Put ad_campaign = 'HOLIDAY_PROMO' in WHERE so other campaigns' impressions never land in a month bucket, and so a large table prunes before aggregation.

Step 2: Bucket by year and month together

The log spans several years. strftime('%Y-%m', impression_time) keeps the year in the key, so February of one year never merges with February of another. Grouping on the month alone would pool them. Repeating the expression in GROUP BY is the portable form; not every engine accepts the alias there.

Step 3: Threshold on clicks, not rows

clicked is a 0/1 flag, so SUM(clicked) per bucket is that month's click count. HAVING SUM(clicked) >= 1 keeps a month only once a real click landed in it.

SELECT strftime('%Y-%m', impression_time) AS click_month
FROM ad_impressions
WHERE ad_campaign = 'HOLIDAY_PROMO'
GROUP BY strftime('%Y-%m', impression_time)
HAVING SUM(clicked) >= 1

Counting rows

HAVING COUNT(*) >= 1 asks whether the month had any impression. Every bucket that exists has at least one row, so the condition filters nothing: 11 months come back.

Summing the flag

HAVING SUM(clicked) >= 1 asks whether any impression was clicked. Months where the campaign was shown but ignored drop out, leaving the 3 that drew a click.

Common follow-up questions

• How would you also return clicks and click-through rate per month? (Add `SUM(clicked) AS clicks` and `1.0 * SUM(clicked) / COUNT(*) AS ctr` to the `SELECT`.)
• What if the business wants months with no clicks listed too, with a zero? (Drop the `HAVING` and return `SUM(clicked)`; the threshold becomes a column.)
• How would you guard against `clicked` arriving as NULL in a future feed? (`SUM` skips NULLs; if NULL means 'not clicked', wrap it as `COALESCE(clicked, 0)` so the intent is explicit.)