Best Day for Ad Revenue

A medium SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

medium

Seniority

L5

Asked in

Forbes

Problem

Only a clicked impression in ad_impressions records revenue: one nobody clicked earned us nothing, even though its revenue is left blank. Lay out each day of the month with its revenue per impression, its revenue per click and its biggest single-impression revenue, the days earning the most per impression first.

Some days of the month convert far better than others.

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 denominator question dressed up as a calendar report. The revenue column is filled only on clicked rows; every unclicked impression carries a blank. AVG(revenue) skips blanks, so it quietly divides by clicks, not impressions, and hands you revenue per click under a revenue-per-impression label. Anyone can bucket by day. What separates candidates is reading that blank as zero earned, folding it back in as a 0, and keeping the per-click figure as its own column. Miss it and every day's per-impression number is inflated by its click rate, zero-click days come back blank, and the day order follows click value instead of what an impression actually earns.

Step 1: Bucket by day of the month

strftime('%d', impression_time) pulls the two-digit day, and the CAST to INTEGER makes 1 through 31 compare as numbers instead of text. Every 5th of every month in every year lands in the same bucket.

Step 2: Let the raw column give the per-click view

Because only clicks carry revenue, AVG(revenue) over the bucket is exactly revenue per click, and MAX(revenue) is the biggest single impression. Both are correct as written; the blanks are supposed to drop out here.

Step 3: Put the unclicked impressions back in the denominator

AVG(CASE WHEN clicked = 1 THEN revenue ELSE 0 END) gives every unclicked row a 0 instead of a blank, so the average divides by every impression in the day. The ELSE 0 is the whole point: it is the one branch that turns 'missing' into 'earned nothing'.

Step 4: Sort on the per-impression figure

ORDER BY revenue_per_impression DESC puts the days where an impression earns the most on top. Days with no clicks sit at 0 at the bottom, which is the honest answer.

The solution

SELECT
  CAST(strftime('%d', impression_time) AS INTEGER) AS day_of_month,
  AVG(CASE WHEN clicked = 1 THEN revenue ELSE 0 END) AS revenue_per_impression,
  AVG(revenue) AS revenue_per_click,
  MAX(revenue) AS max_revenue
FROM ad_impressions
GROUP BY day_of_month
ORDER BY revenue_per_impression DESC

Averaging the column as stored

AVG(revenue) for revenue per impression divides by the clicked rows only. It returns the same number as revenue_per_click, so you ship two columns that say one thing, and a day with no clicks shows NULL.

Counting blanks as zero

AVG(CASE WHEN clicked = 1 THEN revenue ELSE 0 END) divides by every impression. A day with 2 clicks out of 8 impressions reports a quarter of its per-click value, and a zero-click day reports 0.

Common follow-up questions

Common follow-up questions

• How would you add the click-through rate for each day? (`AVG(CASE WHEN clicked = 1 THEN 1.0 ELSE 0 END)`; per-impression revenue is then per-click revenue times that rate.)
• What if `revenue` could also be blank on a clicked impression because of a tracking gap? (Then a blank no longer means zero; you would need to tell the two cases apart before choosing a denominator.)
• How would you split this by `ad_campaign`? (Add it to the grouping; the per-impression logic is unchanged.)