The Long Watch

A medium SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

medium

Seniority

L4

Problem

List every content type in content_items with how many items it has published and its average runtime, longest-running formats first. Articles never record a runtime, but the media team's library audit still counts them as published.

Some formats hold you longer than others. Measure which ones.

Tables

content_items

Columns: content_id INTEGER, title TEXT, content_type TEXT, duration_seconds INTEGER, creator_id INTEGER, publish_date TEXT

products

Columns: product_id INTEGER, product_name TEXT, category TEXT, price REAL, rating REAL, in_stock INTEGER

Expected output

Worked solution and explanation

What this really is

Beneath the 'audit the library' framing, this is a two-population report wearing the costume of a single grouped query. items_published counts every row in a content type; avg_runtime only ever sees the rows that actually have a runtime. Articles hold both facts at once: 40 of them were published, and not one is timed. The candidates who trip reach for one filter to serve both numbers. They add WHERE duration_seconds IS NOT NULL to tidy up the average, and that same clause deletes all 40 articles before the count runs, so the article row vanishes from the report. Get that wrong and the 'published' column stops meaning published.

The trap in one line

Filter first (wrong)

Adding WHERE duration_seconds IS NOT NULL before grouping keeps the average clean, but every article row is gone before the count runs. The format drops out of the report entirely, and the audit shows four formats instead of five.

Trust NULL semantics (right)

Leave every row in. AVG ignores the NULL runtimes on its own, so article survives with items_published = 40 and an empty avg_runtime, and every count stays honest.

SELECT
    content_type,
    COUNT(*) AS items_published,
    AVG(duration_seconds) AS avg_runtime
FROM content_items
GROUP BY content_type
ORDER BY avg_runtime DESC;

Step 1: Break the catalog down by format

GROUP BY content_type gives one row per format. Everything else is a metric computed over the rows inside each group, so the shape of the answer is settled before you write a single aggregate.

Step 2: Count every item, average only the timed ones

COUNT(*) deliberately includes the untimed articles, because they were still published. AVG(duration_seconds) deliberately excludes them, because there is no runtime to average. The asymmetry is the point, and you get it for free by not filtering.

Step 3: Order by the average, longest first

ORDER BY avg_runtime DESC puts the longest formats on top. The article group has a NULL average, and NULLs sort to the bottom under a descending order here, which is exactly where an untimed format belongs on a runtime leaderboard.

One more thing worth noticing: the products table is in scope but never touched. Real schemas hand you more than you need, and part of the job is recognizing which tables the question actually depends on. Nothing about runtime by format lives in products, so it stays out of the query.

Common follow-up questions

• How would you report the average only over items longer than 60 seconds, while keeping `items_published` as the count of every published item? (Tests whether the candidate can attach a filter to one metric via a CASE expression or FILTER clause instead of a query-wide WHERE.)
• Two formats end up with the same average runtime. How should they be ordered, and how would you make that deterministic? (Tests tie-breaking awareness and adding a secondary ORDER BY key.)
• The `products` table was in scope but unused. What would a version of this problem look like that genuinely needed it? (Tests recognition of distractor tables and how added join requirements change the shape.)