# The Long Watch > Some formats hold you longer than others. Measure which ones. Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L4 ## Problem A media team is auditing its content library, where articles carry no runtime while videos, podcasts, and other formats do. For each content type, report how many items were published and the average runtime of that format, longest-running formats first. ## 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: two of them exist, and neither is timed. The candidates who trip do it by reaching for one filter to serve both numbers. They add WHERE duration_seconds IS NOT NULL to tidy up the average, and that same clause quietly deletes the untimed articles from the published count, turning a 2 into a 0. Get that wrong and the 'published' column stops meaning published. > **Two counters that disagree on purpose** > > COUNT(*) counts rows. AVG(duration_seconds) skips NULLs all by itself. Drop them into the same GROUP BY with no WHERE clause and each metric measures exactly the population it should: every item for the count, only the timed items for the average. You do not need to separate the two populations by hand; the functions already do. ### The trap in one line **Filter first (wrong)** Adding WHERE duration_seconds IS NOT NULL before grouping makes the average clean, but article disappears from the result entirely and every remaining count reflects only timed rows. The published figures are now a lie. **Trust NULL semantics (right)** Leave every row in. AVG ignores the NULL runtimes on its own, so article survives with items_published = 2 and avg_runtime = NULL, and all other counts stay honest. > **COALESCE to zero is the other wrong turn** > > The opposite mistake is COALESCE(duration_seconds, 0) before averaging. Now each untimed article counts as a zero-second item and drags its format's average toward the floor. NULL here means 'no runtime recorded', not 'zero seconds'. Averaging over it silently changes the answer. **Both metrics, one pass** ```sql 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; ``` *COUNT(*) sees all rows; AVG ignores the NULL runtimes; no WHERE needed.* #### 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 in this engine NULLs sort to the bottom under a descending order, which is exactly where an untimed format belongs in a runtime leaderboard. > **The tell** > > The signal an interviewer watches for is whether you reach for a WHERE clause at all. A strong candidate leaves the rows in place and trusts AVG's NULL handling. A weaker one filters to clean the average and never notices the articles fell out of the published count. > **In production** > > This is the exact shape behind most content dashboards: 'items published' and 'average watch time' come off the same table, and someone applies one runtime filter for the whole SELECT. The published totals quietly undercount every text or image format, and nobody catches it until a creator asks why their article count reads zero. 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.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/the_long_watch) - [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). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.