Peak Retargeting Revenue Month

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

L4

Asked in

City and County of San Francisco

Problem

Retargeting campaigns are the ones whose name contains 'RETARGET', and every impression they served sits in ad_impressions with the revenue it earned, left blank when it earned nothing. For the growth team's monthly review, find the calendar month in which those campaigns brought in the most ad revenue, with its total revenue, its biggest single-impression revenue and its average revenue per earning impression.

Retargeting revenue. The peak month.

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

This is a monthly argmax over a column that is mostly blank. Anyone can write the month bucket and the SUM. What separates candidates is how they treat a blank revenue. Most retargeting impressions never earned anything, so most monthly totals are NULL. If you sort them carelessly, a month that made nothing can win. If you 'clean' the blanks to zero first, the winning month's average drops from 2.30 to 1.15.

Step 1: Match the campaign family, not one name

Retargeting is a naming convention, so ad_campaign LIKE '%RETARGET%' catches every campaign in the family, including future ones like a hypothetical 'RETARGETING_BROWSE'. An equality on one name works today and breaks the first time marketing launches a sibling.

Step 2: Bucket by calendar month

DATE_TRUNC('month', impression_time) keeps the year inside the bucket. Bucketing by month number alone would pool April of every year into one row, which is a different question.

Step 3: Leave blanks blank

SUM, MAX and AVG already skip NULL. That is exactly the semantics the business wants: the average is per earning impression. Do not add a default to revenue. It looks defensive, but it changes the denominator.

Step 4: Keep empty months off the top

A month where every impression is blank has a NULL total. Some engines sort NULL first when sorting descending, so NULLS LAST decides that placement explicitly before LIMIT 1 takes the winner.

SELECT
  DATE_TRUNC('month', impression_time) AS revenue_month,
  SUM(revenue) AS total_revenue,
  MAX(revenue) AS max_revenue,
  AVG(revenue) AS avg_revenue
FROM ad_impressions
WHERE ad_campaign LIKE '%RETARGET%'
GROUP BY revenue_month
ORDER BY total_revenue DESC NULLS LAST
LIMIT 1

Blanks as zero

AVG(COALESCE(revenue, 0)) divides April's 2.30 by two impressions and reports 1.15. That is the average per impression served, not per impression that earned.

Blanks left blank

AVG(revenue) divides by the one earning impression and reports 2.30. That is the figure the review asked for.

Common follow-up questions

• How would you return every month that ties for the top total instead of an arbitrary one? (Tests replacing `LIMIT 1` with a comparison against the maximum total.)
• Marketing wants the top month per campaign rather than for the whole family. What changes? (Tests moving from a global pick to one pick per `ad_campaign`.)
• If campaign names arrive in mixed case, how do you keep the match working? (Tests normalizing `ad_campaign` before matching.)