# Content Duration Snapshot

> A popularity snapshot by duration.

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

Domain: SQL · Difficulty: easy · Seniority: L3

## Problem

The content team is prioritizing which items to feature on the homepage based on length. List all content items with their ID, title, and duration in seconds, longest first.

## Worked solution and explanation

### Why this problem exists in real interviews

This is a simple SELECT with ORDER BY. It screens for the ability to return all columns and sort by a specific field.

---

### Break down the requirements

#### Step 1: Select all content fields

Return `content_id`, `title`, and `duration_seconds` as specified.

#### Step 2: Sort by duration descending

`ORDER BY duration_seconds DESC` lists longest content first.

---

### The solution

**Simple select with descending sort**

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

> **Cost Analysis**
>
> Scan of 2M rows with a sort. An index on `duration_seconds DESC` would enable an index-ordered scan, avoiding the sort step.

> **Interviewers Watch For**
>
> Whether the candidate selects only the requested columns vs using SELECT *. Selecting specific columns shows attention to the prompt.

> **Common Pitfall**
>
> If `duration_seconds` contains NULLs, they sort first or last depending on the database. PostgreSQL defaults to NULLs-last for DESC. Add `NULLS LAST` explicitly if needed.

---

## Common follow-up questions

- How does NULL sorting differ across databases? _(PostgreSQL: NULLs last in ASC, first in DESC by default. SQL Server: NULLs first always. Tests cross-database awareness.)_
- What if you only needed the top 10 longest? _(Add LIMIT 10 after ORDER BY.)_
- How would you also show the rank of each item by duration? _(Add ROW_NUMBER() OVER (ORDER BY duration_seconds DESC) as a column.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/content_duration_snapshot)
- [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). 100% free data engineering interview prep. Live code execution against Postgres 16, Python 3.11, and Spark sandboxes. No paywall, no premium tier, no signup gate.