Content Duration Snapshot

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

Amadeus

Problem

An editor lining up homepage features by length asks one thing: which items in content_items run longest? She wants every item's ID, title and duration in seconds, longest first, with the lower ID ahead whenever two items run exactly as long.

A popularity snapshot by duration.

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 really tests

Strip off the homepage story and this is a contract about row order: the answer is only right if the sequence is right. Everyone projects the three columns. What separates candidates is whether they trust the table to come back longest-first, or write ORDER BY duration_seconds and quietly get the ascending default. Either way the editor's list opens with the shortest clips, or with whatever order storage happened to return, and an order-graded comparison fails on the very first row.

Step 1: Project exactly the three columns asked for

The editor needs content_id, title and duration_seconds. Do not add a WHERE: every item belongs on the list, including the drafts and the ones whose length was never recorded.

Step 2: Sort duration_seconds descending, explicitly

A relational result is a set until you sort it. DESC is the whole question here: without it you get the shortest content on top.

Step 3: Settle exact ties on content_id

The seed holds pairs of items with the same length: 4500 and 10004600 both run 4760 seconds. Without a second key their relative order is up to the engine, so content_id ascending puts the lower ID ahead every time.

SELECT content_id, title, duration_seconds
FROM content_items
ORDER BY duration_seconds DESC, content_id

Common follow-up questions

• The homepage only has room for ten items. What changes? (A `LIMIT 10` after the sort, and whether a tie at the cut needs the `content_id` rule to decide who makes it.)
• How would you show each item's position on the list? (A `ROW_NUMBER()` over the same ordering, or `RANK()` if equal durations should share a position.)
• The product team wants items with no recorded length shown first so editors can fix them. How? (An explicit `NULLS FIRST` on `duration_seconds`, which also makes the query portable across engines.)