# Year-over-Year Content Launches

> Launch velocity, year over year.

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

Domain: SQL · Difficulty: medium · Seniority: L3

## Problem

Calculate the net change in content items published by each creator in 2026 compared to 2025. Show the creator and the difference (2026 count minus 2025 count).

## Worked solution and explanation

### Why this problem exists in real interviews

This tests cross-year comparison with conditional aggregation. Interviewers check whether you can compute counts for two specific years and subtract them in a single query per creator.

---

### Break down the requirements

#### Step 1: Filter to the two target years

`WHERE STRFTIME('%Y', publish_date) IN ('2024', '2025')` restricts to the relevant years.

#### Step 2: Conditional count per year per creator

Use `SUM(CASE WHEN year = '2025' THEN 1 ELSE 0 END)` and `SUM(CASE WHEN year = '2024' THEN 1 ELSE 0 END)` to count each year separately.

#### Step 3: Compute the difference

Subtract the 2024 count from the 2025 count for each creator.

---

### The solution

**Conditional aggregation for year-over-year delta**

```sql
SELECT
    creator_id,
    SUM(CASE WHEN STRFTIME('%Y', publish_date) = '2025' THEN 1 ELSE 0 END)
    - SUM(CASE WHEN STRFTIME('%Y', publish_date) = '2024' THEN 1 ELSE 0 END) AS yoy_change
FROM content_items
WHERE STRFTIME('%Y', publish_date) IN ('2024', '2025')
GROUP BY creator_id
```

> **Cost Analysis**
>
> STRFTIME prevents index usage on publish_date. A range filter `WHERE publish_date >= '2024-01-01' AND publish_date < '2026-01-01'` would be index-friendly. The GROUP BY produces ~250K creator rows.

> **Interviewers Watch For**
>
> Whether you include creators who only published in one of the two years. The CASE approach naturally handles this: a creator with zero 2024 items shows their full 2025 count as the difference.

> **Common Pitfall**
>
> Subtracting counts from two separate queries without ensuring the same creator set. The conditional aggregation approach avoids this by processing both years in a single GROUP BY.

---

## Common follow-up questions

- How would you show the 2024 and 2025 counts as separate columns? _(Add each SUM(CASE ...) as its own column instead of subtracting inline.)_
- What if you needed the percentage change instead of the absolute difference? _(Divide the difference by the 2024 count, handling zero with NULLIF.)_
- How would you generalize this for any two consecutive years? _(Use LAG on a yearly count table to compute year-over-year differences dynamically.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/year_over_year_content_launches)
- [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). 100% free data engineering interview prep. Live code execution against Postgres 16, Python 3.11, and Spark sandboxes. No paywall, no premium tier, no signup gate.