# Losing Altitude

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

Canonical URL: <https://datadriven.io/problems/losing_altitude>

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.

## Tables

### ad_impressions

- impression_id INTEGER
- user_id INTEGER
- ad_campaign TEXT
- impression_time TEXT
- clicked INTEGER
- revenue REAL

Sample rows (10 of 200):

| impression_id | user_id | ad_campaign | impression_time | clicked | revenue |
| --- | --- | --- | --- | --- | --- |
| 253 | 100 | BRAND_AWARENESS_Q1 | 2026-02-02 01:07:00 | 0 | NULL |
| 306 | 197 | PRODUCT_LAUNCH_X | 2026-03-03 02:14:00 | 0 | NULL |
| 359 | 294 | RETARGETING_CART | 2026-04-04 03:21:00 | 0 | NULL |
| 412 | 391 | HOLIDAY_PROMO | 2026-05-05 04:28:00 | 0 | NULL |
| 465 | 488 | NEW_USER_ACQU | 2026-06-06 05:35:00 | 1 | 0.2 |
| 518 | NULL | LOYALTY_PROGRAM | 2026-07-07 06:42:00 | 0 | NULL |
| 571 | 682 | FLASH_SALE_48H | 2026-08-08 07:49:00 | 0 | NULL |
| 624 | 779 | SUMMER_SALE_2024 | 2026-09-09 08:56:00 | 0 | NULL |
| 677 | 876 | BRAND_AWARENESS_Q1 | 2026-01-10 09:03:00 | 0 | NULL |
| 730 | 973 | PRODUCT_LAUNCH_X | 2026-02-11 10:10:00 | 1 | 0.35 |

## Expected output

First 3 of 3 rows:

| ad_campaign |
| --- |
| NEW_USER_ACQU |
| PRODUCT_LAUNCH_X |
| SUMMER_SALE_2024 |

## 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

**Three CTEs and a self-join across halves**

```sql
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
```

> **Collapse before you rank**
>
> `ad_impressions` is 350,000,000 rows and every one of them is in scope, so the scan is unavoidable. What keeps it cheap is the order of work: `half_clicks` collapses the table to at most a few hundred campaign-half rows, and the window and the self-join only ever touch those.

> **Direction of the inequality is the seniority tell**
>
> They want `PARTITION BY year_half` in the placing, the inner join enforcing both halves, and the inequality pointing the right way. Strong candidates also note that `COUNT(CASE WHEN clicked = 1 THEN 1 END)` and `SUM(clicked)` agree here because `clicked` is 0/1, but the `CASE` form survives `clicked` becoming nullable.

> **Three ways to answer a different question**
>
> Dropping `PARTITION BY year_half` places both halves in one pool. Swapping in `RANK()` skips places after a tie and can push a campaign below its first-half place without it losing ground. Reversing the inequality returns the campaigns that climbed.

---

## 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'.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/losing_altitude)
- [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.