Content Mix

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

Problem

Which content formats run longest on average, and how much of each format's catalog is missing a duration or a credited creator? For the editorial team's budget review, list every content type in content_items with its item count, average duration in seconds and that incomplete share as a percentage to one decimal, longest-running formats first.

One content format to bet the quarter on.

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

A completeness audit in a budget costume

This looks like a three-column format comparison, and the average is the easy part: AVG(duration_seconds) already skips the gaps. The question underneath is whether you can count incomplete items per row, not per column. Anyone can write COUNT(*) - COUNT(duration_seconds). With two fields in play, the tempting move is to add two of those differences. An 'article' with no duration and no creator then counts twice, and the share overstates the problem, sometimes past 100. The other reflex, WHERE duration_seconds IS NOT NULL, is worse: it erases the 'article' format entirely.

Subtract per column

(COUNT(*) - COUNT(duration_seconds)) + (COUNT(*) - COUNT(creator_id)) counts gaps, not items. An item missing both fields adds 2, so 'article' reads above 100.

Flag per row

SUM(CASE WHEN duration_seconds IS NULL OR creator_id IS NULL THEN 1 ELSE 0 END) asks one question per row. An item missing both fields adds 1, and 'article' reads exactly 100.0.

Building it

Step 1: Keep every item in its format

GROUP BY content_type with no filter in front of it. The moment you drop rows to tidy the average, you lose the evidence the third column is supposed to report, and 'article' leaves the result.

Step 2: Count rows, average values

COUNT(*) is the item total because it counts rows, not values. AVG(duration_seconds) averages only recorded durations, and for a format with none it returns NULL rather than a misleading zero.

Step 3: Flag incomplete items with a conditional sum

The CASE asks whether this item lacks a duration OR a creator, so each incomplete item becomes exactly one 1. Divide by COUNT(*), scale by 100.0 so the division stays decimal, and ROUND(..., 1).

Step 4: Sort longest first

ORDER BY avg_duration DESC puts the longest-running formats on top, which is the order the budget review reads.

SELECT
    content_type,
    COUNT(*) AS item_count,
    AVG(duration_seconds) AS avg_duration,
    ROUND(100.0 * SUM(CASE WHEN duration_seconds IS NULL OR creator_id IS NULL THEN 1 ELSE 0 END) / COUNT(*), 1) AS incomplete_pct
FROM content_items
GROUP BY content_type
ORDER BY avg_duration DESC

Common follow-up questions

• When would `COUNT(*)` and `COUNT(duration_seconds)` give different answers here? (Tests whether the candidate knows `COUNT(*)` counts rows and `COUNT(duration_seconds)` counts non-null values.)
• How would the query change if a missing `publish_date` also made an item incomplete? (Tests adding a third condition to the same `CASE` rather than another counted column.)
• How would you add the median `duration_seconds` per `content_type`? (Tests `PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY duration_seconds)` and that it also ignores nulls.)