No Dead Months

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

L4

Asked in

Uber

Problem

Which ad campaigns in ad_impressions went at least one calendar month without a single click, judging each campaign only on the months it actually served impressions? Return each of those campaigns with the revenue from its single heaviest month.

Every month it ran, it landed a click. Among those, find the ones whose worst month of spend stayed lowest.

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 problem is really testing

This is a two-level aggregation dressed up as a campaign health check. The month lives inside impression_time, so the set of months a campaign ran only exists after you roll impressions up to campaign-month. Anyone can count clicks. The trap is answering 'went a month without a click' at campaign level: SUM(clicked) = 0 only catches campaigns that never got clicked at all, and a campaign with a dry March and a busy June slips straight past it. The second trap is the revenue column: 'heaviest month' is the biggest monthly total, not the biggest single impression and not lifetime revenue.

Building it up

Step 1: Collapse to one row per campaign-month

Group ad_impressions by ad_campaign and the year-month of impression_time. monthly_clicks counts rows where clicked = 1; monthly_spend sums revenue. Revenue is null on unclicked rows and SUM skips nulls, so nothing needs patching. Months with no impressions never appear, which is exactly the 'only months it ran' rule.

Step 2: Flag campaigns with a dead month

Group the monthly rows by ad_campaign and keep those where MIN(monthly_clicks) = 0. One zero among the active months is enough to flag the campaign, no matter how busy its other months were.

Step 3: Report the peak month

In the same second-level aggregate, MAX(monthly_spend) is the heaviest month's revenue. Return it as peak_month_revenue next to the name.

The solution

WITH monthly AS (
    SELECT ad_campaign,
           STRFTIME('%Y-%m', impression_time) AS month,
           SUM(CASE WHEN clicked = 1 THEN 1 ELSE 0 END) AS monthly_clicks,
           SUM(revenue) AS monthly_spend
    FROM ad_impressions
    GROUP BY ad_campaign, STRFTIME('%Y-%m', impression_time)
)
SELECT ad_campaign,
       MAX(monthly_spend) AS peak_month_revenue
FROM monthly
GROUP BY ad_campaign
HAVING MIN(monthly_clicks) = 0;

Naive (one level)

HAVING SUM(clicked) = 0 and MAX(revenue) on raw rows. Only campaigns that were never clicked survive, and the 'peak' is one impression's revenue.

Correct (per-month extremes)

HAVING MIN(monthly_clicks) = 0 and MAX(monthly_spend) over the monthly CTE. Any single dry month flags the campaign, and the peak is a whole month's revenue.

Common follow-up questions

• Now flag any month with fewer than 5 clicks as dead. What changes? (Tests moving a threshold into the monthly definition without breaking the second-level logic.)
• How would you also catch campaigns that skipped a calendar month entirely between their first and last impression? (Tests understanding that months with no rows are invisible to `MIN` and need a calendar to detect.)
• The team wants to know which month was the peak, not just its revenue. How do you add that? (Tests returning a second attribute of the extreme row, not just the extreme value.)