Year-over-Year Content Launches

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

L3

Asked in

Tesla

Problem

A content analyst noticed the year-over-year output figures for the platform's video side are inflated by articles and podcasts. Rebuild them from content_items counting only video, short and livestream items: every creator who has ever published one gets a line with their 2026 count minus their 2025 count. Items with no creator on record stay together as one uncredited line.

Launch velocity, year over year.

Tables

content_items

Columns: content_id INTEGER, title TEXT, content_type TEXT, duration_seconds INTEGER, creator_id INTEGER, publish_date TEXT

Expected output

Worked solution and explanation

Two filters that look alike

This is a pivot of two years into one signed number, dressed up as a report cleanup. Anyone can write the two conditional sums. What separates candidates is where each restriction goes: the format rule belongs in WHERE because it decides which items exist, while the years belong inside the CASE because they only decide which side of the subtraction an item lands on. Push the years into WHERE and every creator whose video output sat in earlier years vanishes instead of showing a flat zero.

Step 1: Throw out the non-video items first

WHERE content_type IN ('video', 'short', 'livestream') removes articles and podcasts before anything is counted. That is the whole reason the old figures were wrong, so it is a row filter, applied once.

Step 2: Bucket each item by year inside the sum

SUM(CASE WHEN STRFTIME('%Y', publish_date) = '2026' THEN 1 ELSE 0 END) counts this year's items; the twin with '2025' counts last year's. Items from other years add 0 to both, which is exactly what lets a flat creator stay on the list.

Step 3: Subtract in one pass per creator

GROUP BY creator_id and subtract the two sums. No join, no second scan, and the NULL creator falls out as its own group: that is the uncredited line.

SELECT
    creator_id,
    SUM(CASE WHEN STRFTIME('%Y', publish_date) = '2026' THEN 1 ELSE 0 END)
    - SUM(CASE WHEN STRFTIME('%Y', publish_date) = '2025' THEN 1 ELSE 0 END) AS net_difference
FROM content_items
WHERE content_type IN ('video', 'short', 'livestream')
GROUP BY creator_id

SELECT
    creator_id,
    SUM(CASE WHEN STRFTIME('%Y', publish_date) = '2026' THEN 1 ELSE 0 END)
    - SUM(CASE WHEN STRFTIME('%Y', publish_date) = '2025' THEN 1 ELSE 0 END) AS net_difference
FROM content_items
WHERE content_type IN ('video', 'short', 'livestream')
  AND STRFTIME('%Y', publish_date) IN ('2025', '2026')
GROUP BY creator_id

Row filter

content_type IN (...) in WHERE: decides whether an item counts at all.

Bucket condition

The year test inside CASE: decides which count an item feeds, never whether its creator appears.

Common follow-up questions

• How would you show the 2026 and 2025 counts as their own columns next to the difference? (Each conditional sum becomes a named column.)
• What if the report wanted percentage change instead of the absolute difference? (Divide by last year's count, guarding the zero case with `NULLIF`.)
• How would you generalize this to every pair of consecutive years? (Count per creator per year, then compare each year to the previous one.)