Content Sorted by Duration

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

L3

Asked in

Allstate

Problem

Nobody can tell from our content report how far each piece in content_items runs short of the longest one in the whole catalog. Return every item with all of its columns, its duration, and that shortfall, both figures in seconds as decimals.

The catalog, sorted by length.

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

One number, every row

This is a broadcast of one table-wide scalar dressed up as a content report. Anyone can find the longest duration. The trap is computing it in the same pass as the detail rows: SELECT *, MAX(duration_seconds) - duration_seconds mixes a bare aggregate with row-level columns, and SQLite does not error. It quietly collapses the whole catalog to one arbitrary row. The team asked for every item and gets one. The fix is to compute the maximum on its own, hand that single value to every row, then cast both figures so they come back as decimals.

Step 1: Compute the longest duration once

(SELECT MAX(duration_seconds) FROM content_items) is a scalar subquery: one row, one column, usable like a constant inside the outer SELECT. MAX skips the NULL durations, so the value is the true catalog-wide longest.

Step 2: Keep every row and every column

SELECT * over content_items with no WHERE and no grouping keeps all 200 items, including the 40 with no duration. Nothing is aggregated in the outer query, so nothing collapses.

Step 3: Cast both figures to decimals

CAST(duration_seconds AS REAL) gives duration_decimal. The gap is an integer minus an integer, which stays an integer, so the whole difference gets its own cast to become diff_from_longest. An item with a NULL duration gets NULL in both.

SELECT
    *,
    CAST(duration_seconds AS REAL) AS duration_decimal,
    CAST((SELECT MAX(duration_seconds) FROM content_items) - duration_seconds AS REAL) AS diff_from_longest
FROM content_items

-- One row comes back, not the catalog
SELECT *, MAX(duration_seconds) - duration_seconds AS diff_from_longest
FROM content_items

Scalar subquery

(SELECT MAX(duration_seconds) FROM content_items) is evaluated once, and the outer scan reads it as a constant.

Window over the whole table

MAX(duration_seconds) OVER () puts the same value on every row, but a window with no partition key must see the entire table first, so a distributed engine funnels every row through one worker.

Common follow-up questions

• How does `MAX(duration_seconds) OVER ()` differ from the scalar subquery? (Same values on every row. Tests whether you know a window with no partition key has nothing to spread its work across.)
• How would you also show each item's position by duration, longest first? (A window ordered by `duration_seconds` descending. Watch where the `NULL` durations land.)
• What if the team wanted the gap from the average duration instead? (Swap `MAX` for `AVG` in the subquery. Items above the average then get a negative gap.)