# The Longest Take

> The content that held the number-one spot.

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

Domain: SQL · Difficulty: easy · Seniority: L4

## Problem

A media platform's 2026 retrospective spotlights the longest piece in each content format, drawn from everything published since 2026. Return the content items whose runtime is the longest ever recorded for their format, with their unique content IDs and titles.

## 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. The skill: for each `content_type`, find its all-time longest `duration_seconds`, then return only rows that both equal that max and were published since the cutoff year. 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-YEAR, promoting formats whose real record holder is older and returning content that never actually held the top spot.

---

### Break down the requirements

#### Step 1: Per-type max via correlated subquery

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

#### Step 2: Filter the output

Keep rows where `duration_seconds` equals that per-type max AND `publish_date >= '2026-01-01'`. The date test lives out on the returned rows, never inside the subquery's WHERE, so it narrows the output without shifting the max.

#### Step 3: Distinct id + title

`DISTINCT content_id, title` drops any accidental duplicate; `ORDER BY content_id` gives the smallest-first ordering the preview shows. A null `duration_seconds` fails the equality (null never equals anything), so items with no recorded runtime drop out on their own.

---

### The solution

**PER-TYPE MAX VIA CORRELATED SCALAR SUBQUERY**

```sql
SELECT DISTINCT ci.content_id, ci.title
FROM content_items ci
WHERE ci.duration_seconds = (
        SELECT MAX(ci2.duration_seconds)
        FROM content_items ci2
        WHERE ci2.content_type = ci.content_type
    )
  AND ci.publish_date >= '2026-01-01'
ORDER BY ci.content_id
```

> **It's a self-join in disguise**
>
> The subquery joins the table to itself on `content_type`: one alias supplies candidate rows, the other supplies the per-type maximum. Recognizing that this is a self-join expressed as a subquery, not a plain single-table scan, is what lets you reason about its cost.

> **Cost Analysis**
>
> With a composite index on `(content_type, duration_seconds)` the inner MAX is an index probe, so the plan is roughly O(N log N) rather than a per-row table scan. At larger scale an equivalent one-pass form, MAX(duration_seconds) OVER (PARTITION BY content_type), collapses it to a single scan; keep the date filter outside the window either way.

> **Interviewers Watch For**
>
> Where the date filter lives. Inside the subquery's WHERE it becomes longest-since-YEAR (wrong); on the outer query, on the returned rows, it stays the all-time max (right). Naming that split out loud is the tell.

> **Common Pitfall**
>
> `GROUP BY content_type` with `MAX(duration_seconds)`, then trying to match `HAVING duration_seconds = MAX(...)`, collapses to one row per type and loses ties. You need the max compared against each individual row, not aggregated away.

---

### COMMON FOLLOW-UP QUESTIONS

## Common follow-up questions

- Rewrite this 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, same result.)_
- What if the prompt wanted the top item per type only among items published since YEAR? _(Move the date filter inside the subquery so it scopes the per-type max. Numbers differ when a pre-YEAR row is the type's longest.)_
- How would you index this table? _(Composite index on (content_type, duration_seconds) turns the inner MAX into an index probe; add publish_date for a covering index across all three predicates.)_

## Related

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