The Longest Take

An easy SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

easy

Seniority

L4

Asked in

Spotify

Problem

Which pieces in content_items published since 2026 hold the longest runtime their format has ever recorded? The analyst assembling the retrospective wants each one's content ID and title, listed once.

The content that held the number-one spot.

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

What this is really asking

Beneath the retrospective framing this is a compute-the-group-max, then keep-the-rows-that-equal-it problem, with a scope split hiding in the middle. For each content_type you need its all-time longest duration_seconds, then only the rows that equal it and were published since 2026. Anyone can find a per-type max. The trap is scope: fold the date filter into the max and you redefine it as longest-since-2026, promoting formats whose real record holder is older and returning content that never actually held the top spot.

The second bite is quieter. Setting each holder against the rest of its format means pairing it with every piece of that format, and that pairing multiplies rows. Miss it and a three-piece retrospective comes back as dozens of rows of the same titles.

Build it in order

Step 1: Find each format's all-time record

For each holder row, a correlated scalar subquery reads MAX(peer.duration_seconds) over the same table filtered to peer.content_type = holder.content_type. It returns one value per row: that format's all-time longest runtime, over every item ever published, untouched by the outer date filter.

Step 2: Keep the record holders since 2026

Keep rows where holder.duration_seconds equals that record AND holder.publish_date >= '2026-01-01'. The date test lives on the outer rows, never inside the subquery, so it narrows what you return without moving the record. A NULL duration fails the equality, so pieces with no recorded runtime drop out on their own.

Step 3: Set each holder against its format as rival

Join content_items to itself as rival on content_type. Each holder now meets every piece of its format, the whole field it tops, itself included. The join key is a plain equality, so the pairing is exactly the format's roster and nothing more.

Step 4: Collapse the fan-out with DISTINCT

Every holder now appears once per rival. SELECT DISTINCT holder.content_id, holder.title folds those copies back to one row per record holder, while tied holders survive because their content_id values differ.

SELECT DISTINCT holder.content_id, holder.title
FROM content_items holder
JOIN content_items rival
  ON rival.content_type = holder.content_type
WHERE holder.duration_seconds = (
        SELECT MAX(peer.duration_seconds)
        FROM content_items peer
        WHERE peer.content_type = holder.content_type
    )
  AND holder.publish_date >= '2026-01-01'

Without the collapse

The join to rival emits one row per format mate, so each holder repeats once for every piece in its format.

With the collapse

DISTINCT on content_id, title returns each record holder once; ties stay separate because their ids differ.

Common follow-up questions

• How would you drop the `DISTINCT` without changing the result? (An `EXISTS` test against the format stops at the first match, so there is no fan-out to collapse.)
• What if the ask were the longest piece per format among only the 2026 releases? (Move the date filter inside the subquery so it scopes the record; results differ when a pre-2026 piece is the format's longest.)
• Rewrite the record lookup without a correlated subquery. (`MAX(duration_seconds) OVER (PARTITION BY content_type)` in a CTE, filtered on the outer query: one pass instead of a per-row probe.)