# No Dead Months > Every month it ran, it landed a click. Among those, find the ones whose worst month of spend stayed lowest. Canonical URL: Domain: SQL · Difficulty: hard · Seniority: L4 ## Problem We're building a leaderboard of ad campaigns, but a campaign only earns a spot if it drew at least one click in every calendar month it was active. Among the campaigns that clear that bar, order them by their single heaviest month of revenue, lowest peak first, and return just the campaign name. ## Worked solution and explanation ### What this problem is really testing This is a two-level aggregation hiding behind ad-efficiency language: you roll impressions up to a monthly grain, then aggregate ACROSS months per campaign. The skill being probed is whether you can keep those two levels straight. The trap is the word 'every': candidates instinctively reach for SUM or AVG of clicks, but 'at least one click in every month it ran' is a floor condition, which is MIN of the monthly click counts. Miss that and you let through a campaign that went dark for a month but made up for it elsewhere. The second trap is 'heaviest month of revenue', which is the per-campaign MAX of monthly spend, not the total. Rank by total and a campaign with many cheap months loses to one with a single expensive spike, which is exactly backwards. > **Trick to solving** > > Do the aggregation in two passes. First collapse to (campaign, month) so each row is one month of one campaign. Then GROUP BY campaign again and let MIN and MAX read across those monthly rows. The whole problem falls out once you see that the qualification test and the ranking metric both live at the second level. --- ### Building it up #### Step 1: Collapse to one row per campaign-month Group by ad_campaign and the year-month of impression_time. monthly_clicks is the sum of the clicked flag (it is 0/1, so summing it counts clicks); monthly_spend is the sum of revenue. Revenue is null on unclicked rows, and SUM skips nulls, so no COALESCE is needed. This CTE is the grain everything downstream reasons about. #### Step 2: Filter to campaigns with no dead month Group the monthly rows by campaign and keep only those where MIN(monthly_clicks) >= 1. Because MIN scans every populated month, a single dead month drops the whole campaign. This is the 'every month' requirement expressed as a floor. #### Step 3: Rank by the peak month, lowest first In the same second-level aggregation, order the survivors by MAX(monthly_spend) ascending. MAX picks each campaign's worst (heaviest) month, and ascending puts the campaign whose worst month was cheapest at the top. #### Step 4: Make the order deterministic Add ad_campaign ascending as a tiebreaker so two campaigns with an identical peak come back in a stable order, and select only the campaign name as the prompt asks. No LIMIT: the leaderboard is the full ranked list of qualifying campaigns. --- ### The solution **Two-level rollup with a floor filter and a peak-based ranking** ```sql 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 FROM monthly GROUP BY ad_campaign HAVING MIN(monthly_clicks) >= 1 ORDER BY MAX(monthly_spend) ASC, ad_campaign ASC; ``` **Naive (totals)** HAVING SUM(clicked) >= 1 and ORDER BY SUM(revenue). A campaign with one dead month still passes because its other months carry the total, and a campaign with many cheap months is ranked above one with a single expensive spike. **Correct (per-month extremes)** HAVING MIN(monthly_clicks) >= 1 and ORDER BY MAX(monthly_spend). The floor rejects any campaign that ever went quiet, and the peak ranks by the worst single month, which is the metric the prompt actually asks for. > **Common pitfall** > > The most common wrong answer collapses the two levels into one. Writing MIN(clicked) over the raw table gives the minimum of a 0/1 flag, which is 0 for almost every campaign, so nothing qualifies. You must compute the monthly click COUNTS first, then take MIN of those counts. > **Interviewers watch for** > > Saying out loud 'this needs a monthly rollup, then a per-campaign aggregate over the months' before writing a line signals that you see the grain, not just the syntax. Reaching for MIN/MAX at the right level, rather than patching a single GROUP BY, is the tell that separates a senior answer here. > **Why the plan stays cheap** > > On the real table this scans 500M rows, and the first GROUP BY is the pressure point: it reduces impressions to at most (campaigns x months) rows, a few thousand, before the second aggregation runs on almost nothing. A partition on impression_time lets the engine prune months when the question is later scoped to a window. CTEs are optimization fences in some engines, so if this became a hot path you would confirm the monthly aggregate is pushed down rather than materialized whole. ## Common follow-up questions - Now require at least 100 clicks every month instead of at least 1. What changes, and does the rest of the query stay the same? _(Tests whether the candidate can move a threshold from the outer aggregate into the monthly definition without breaking the floor logic.)_ - How would you additionally exclude campaigns that skipped a calendar month entirely between their first and last impression? _(Tests understanding that 'gap' months (no rows) are invisible to MIN and would need a calendar to detect.)_ - The team also wants each campaign's peak monthly revenue shown next to its name. How do you add that? _(Tests whether the candidate can return the peak value alongside the name and reason about column selection under GROUP BY.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/no_dead_months) - [SQL Interview Questions](https://datadriven.io/sql-interview-questions) - [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep) - [Daily Challenge](https://datadriven.io/daily) --- Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.