Losing Altitude

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

L5

Asked in

Meta

Problem

List, alphabetically, the ad campaigns that stood lower from July through December than from January through June, pooling every year in ad_impressions into those two halves. The growth team places campaigns within each half by how many of their impressions were clicked, most first, with tied campaigns sharing a place and no place skipped after them.

Two months apart, the same campaigns. Find the ones that slipped.

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

Strip the marketing costume and this is a per-partition standing compared against itself across two slices of the calendar. The skill being probed: can you compute a place INSIDE each half of the year independently, then line the same campaign up next to itself and read whether it moved? Anyone can count clicks. The trap is reading 'stood lower' as fewer clicks: a campaign whose clicks grew while its peers grew faster still slipped, and one whose clicks fell while it held the top spot never did. Compare raw totals and you flag the wrong set.

Break down the requirements

Step 1: Tag each impression with its half of the year

CASE WHEN EXTRACT(MONTH FROM impression_time) <= 6 THEN 1 ELSE 2 END drops every impression into a half, ignoring the year on purpose: the question is seasonal, so every June lands beside every January. Naming it year_half once in tagged means nothing downstream repeats the expression.

Step 2: Count clicked impressions per campaign and half

Group tagged by ad_campaign and year_half. COUNT(CASE WHEN clicked = 1 THEN 1 END) drops unclicked impressions as NULL: a CASE with no ELSE returns NULL, and COUNT ignores NULLs.

Step 3: Place campaigns within each half independently

DENSE_RANK() OVER (PARTITION BY year_half ORDER BY total_clicks DESC) gives each campaign a no-gaps place within its own half. PARTITION BY year_half is the load-bearing clause: without it the place is global across both halves and the comparison collapses into which half was busier.

Step 4: Join the standings to themselves and compare

Join ranked as first_half to ranked as second_half on ad_campaign, pinning year_half to 1 and 2 in the join so each campaign pairs exactly once. The inner join enforces the both-halves requirement. Keep second_half.half_place > first_half.half_place, since a larger number later in the year means the campaign fell.

The solution

WITH tagged AS (
  SELECT ad_campaign,
         clicked,
         CASE WHEN EXTRACT(MONTH FROM impression_time) <= 6 THEN 1 ELSE 2 END AS year_half
  FROM ad_impressions
),
half_clicks AS (
  SELECT ad_campaign,
         year_half,
         COUNT(CASE WHEN clicked = 1 THEN 1 END) AS total_clicks
  FROM tagged
  GROUP BY ad_campaign, year_half
),
ranked AS (
  SELECT ad_campaign,
         year_half,
         DENSE_RANK() OVER (PARTITION BY year_half ORDER BY total_clicks DESC) AS half_place
  FROM half_clicks
)
SELECT first_half.ad_campaign
FROM ranked AS first_half
INNER JOIN ranked AS second_half
  ON second_half.ad_campaign = first_half.ad_campaign
 AND first_half.year_half = 1
 AND second_half.year_half = 2
WHERE second_half.half_place > first_half.half_place
ORDER BY first_half.ad_campaign

Common follow-up questions

• How would you also report by how many places each campaign dropped? (Adds `second_half.half_place - first_half.half_place` as a delta and orders by it descending. Dense places keep the delta meaningful because none are skipped.)
• What if the comparison had to run half by half across every year instead of pooling them? (Moves the half into the partition with the year, then uses `LAG(half_place) OVER (PARTITION BY ad_campaign ORDER BY year_num, year_half)` instead of a fixed two-way self-join.)
• What if a campaign has impressions but zero clicks in the second half? (It still has a row in `half_clicks` with `total_clicks` of 0, so it lands at the bottom. Separate 'ran but flopped' from 'never ran'.)