# Top Content by Watch Time > Some content holds attention. Others get skipped. Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L4 · Asked in: BeyondTrust ## Problem A media platform wants to know which content holds the most attention among its signed-in audience, so it totals watch time only from views tied to a logged-in viewer. To keep untracked traffic off the board, it also drops any content that never drew a view from a recognized device. Return the content in the top three tiers by total watch time, where items sharing a total belong to the same tier, showing the content ID, title, and total watch time, highest first. ## Tables ### content_items - content_id INTEGER - title TEXT - content_type TEXT - duration_seconds INTEGER - creator_id INTEGER - publish_date TEXT Sample rows (10 of 200): | content_id | title | content_type | duration_seconds | creator_id | publish_date | | --- | --- | --- | --- | --- | --- | | 243 | The Ultimate Guide to AI Trends | article | NULL | 197 | 2026-02-02 | | 286 | Top 10 Mobile Apps | podcast | 154 | 294 | 2025-03-03 | | 329 | Understanding Machine Learning [DRAFT] | short | 201 | 391 | 2026-04-04 | | 372 | Mastering Tech Startups | livestream | 248 | 488 | 2025-05-05 | | 415 | Introduction to Cybersecurity | video | 295 | 585 | 2026-06-06 | | 458 | Deep Dive: Cloud Computing | article | NULL | 682 | 2025-07-07 | | 501 | Behind the Scenes of Product Management | podcast | 389 | 779 | 2026-08-08 | | 544 | Breaking Down DevOps | short | 436 | 876 | 2025-09-09 | | 587 | Exploring Web Development | livestream | 483 | NULL | 2026-01-10 | | 630 | How to Data Science | video | 530 | 1070 | 2025-11-11 | ### content_views - view_id INTEGER - content_id INTEGER - user_id INTEGER - device_id INTEGER - viewed_at TEXT - watch_seconds INTEGER Sample rows (10 of 200): | view_id | content_id | user_id | device_id | viewed_at | watch_seconds | | --- | --- | --- | --- | --- | --- | | 7053 | 372 | 1361 | 404 | 2026-02-02 01:07:00 | 41 | | 7106 | 501 | 2622 | 607 | 2026-03-03 02:14:00 | 72 | | 7159 | 630 | 3883 | 810 | 2026-04-04 03:21:00 | 103 | | 7212 | 759 | 5144 | 1013 | 2026-05-05 04:28:00 | 134 | | 7265 | 888 | 6405 | 1216 | 2026-06-06 05:35:00 | 165 | | 7318 | 1017 | 7666 | 1419 | 2026-07-07 06:42:00 | 196 | | 7371 | 1146 | NULL | 1622 | 2026-08-08 07:49:00 | 227 | | 7424 | 1275 | 488 | 1825 | 2026-09-09 08:56:00 | 258 | | 7477 | 1404 | 1749 | NULL | 2026-01-10 09:03:00 | 289 | | 7530 | 1533 | 3010 | 2231 | 2026-02-11 10:10:00 | 320 | ## Expected output First 3 of 3 rows: | content_id | title | lifetime_value | | --- | --- | --- | | 1877 | Exploring Web Development | 13534 | | 2780 | How to Data Science | 10776 | | 3382 | Mastering Tech Startups | 10146 | ## Worked solution and explanation ### What this problem is really testing Strip the 'watch time' costume and this is a scoped aggregate ranked into tie-tiers, guarded by two quality gates. The skill: sum watch time per content, but only over views that survive both gates, then return whole tiers of totals rather than a fixed row count. Anyone can write the join and the SUM. Three things separate candidates. First, the scope: count anonymous views (`user_id` is null) and every total inflates, so the wrong content floats up. Second, the untracked gate: content whose views never carry a `device_id` is traffic you cannot trust, and it has to be dropped after grouping, which is exactly what a COUNT-based HAVING is for. Third, the tie semantics: 'top three tiers including ties' rules out LIMIT 3 and `ROW_NUMBER`, which both cut a tied third tier. Miss any one and your answer is confidently wrong. --- ### Building it up #### Step 1: Scope to signed-in views The metric is watch time from the signed-in audience, so add `WHERE cv.user_id IS NOT NULL`. This is not decorative: about a tenth of views are anonymous and they carry real watch seconds, so dropping this filter changes totals and can reorder the top three. Note it filters the VIEWER, not the duration; a signed-in view can still have a null `watch_seconds`, and SUM simply ignores that value. #### Step 2: Aggregate per content First CTE: `INNER JOIN content_items` to `content_views` on `content_id`, then `GROUP BY ci.content_id, ci.title` and `SUM(cv.watch_seconds) AS lifetime_value`. Carry `title` in the group key so you can return it without a second pass over the dimension table. #### Step 3: Drop untracked content A content whose every view has a null `device_id` is untracked traffic you should not rank. That is a condition on the GROUP, not on one row, so it belongs in `HAVING COUNT(cv.device_id) >= 1`: COUNT ignores nulls, so a content with no recognized device counts zero and is dropped. Do this in HAVING, not WHERE: a per-view `device_id IS NOT NULL` would keep only some of a content's views and mis-total the rest. #### Step 4: Rank the totals into tiers Second CTE: `DENSE_RANK() OVER (ORDER BY lifetime_value DESC)`. Dense, not regular RANK: regular RANK skips numbers after a tie, so two items tied at tier 1 push RANK to 3 and `tier <= 3` would grab a fourth-place item while pretending it saw three tiers. `DENSE_RANK` gives 1, 1, 2, so three tiers means three genuine total levels. #### Step 5: Keep the top three tiers, ordered Outer `SELECT ... WHERE tier <= 3`, then `ORDER BY lifetime_value DESC, content_id` so the output is deterministic even when two items tie. This is the only place `<= 3` appears; do not also add `LIMIT 3` or you reintroduce the truncation you just avoided. --- ### The solution **The Ones Who Hold Attention** ```sql WITH content_stats AS ( SELECT ci.content_id, ci.title, SUM(cv.watch_seconds) AS lifetime_value FROM content_items ci INNER JOIN content_views cv ON ci.content_id = cv.content_id WHERE cv.user_id IS NOT NULL GROUP BY ci.content_id, ci.title HAVING COUNT(cv.device_id) >= 1 ), ranked AS ( SELECT content_id, title, lifetime_value, DENSE_RANK() OVER (ORDER BY lifetime_value DESC) AS tier FROM content_stats ) SELECT content_id, title, lifetime_value FROM ranked WHERE tier <= 3 ORDER BY lifetime_value DESC, content_id ``` > **Cost Analysis** > > The 1B-row `content_views` is the fact table, the 500K `content_items` the dim. The planner builds a hash on `content_items.content_id` and probes it from `content_views`, applying `user_id IS NOT NULL` during the scan so anonymous rows never enter the aggregate. The group-by collapses to at most ~480K content groups, and the `HAVING COUNT(device_id) >= 1` is checked on those groups for free from the same pass. A single sort then feeds `DENSE_RANK`. Because the metric is lifetime, no `viewed_at` pruning helps; the win is pushing the null filter into the scan. > **Interviewers Watch For** > > Say out loud: do anonymous views count, what about content with no device behind any view, and does 'top three' mean rows or totals? A strong candidate scopes to signed-in viewers before summing, reaches for a HAVING to drop untracked content rather than a per-view filter, and names that LIMIT 3 and `ROW_NUMBER` fail the tie spec, choosing `DENSE_RANK`. Volunteering these before writing is the tell that separates senior from mid. > **Common Pitfall** > > Three classic misses. Forgetting `user_id IS NOT NULL`, so anonymous views pad every total and the order drifts. Filtering `device_id` per view instead of gating the group with `HAVING COUNT(device_id) >= 1`, which mis-totals partly-tracked content. And writing `RANK()` instead of `DENSE_RANK()`: with two items tied at tier 1, RANK skips to 3, and `tier <= 3` returns the two ties plus one more while never producing a real tier 2. --- ### Common follow-up questions ## Common follow-up questions - How would you return the top three tiers per `content_type` instead? _(Probes whether you add `PARTITION BY ci.content_type` to the `DENSE_RANK` window and carry `content_type` through the first CTE.)_ - Replays can push `watch_seconds` above the content's duration. How would you cap it? _(Tests `LEAST(cv.watch_seconds, ci.duration_seconds)` inside the SUM, showing comfort with row-level expressions inside an aggregate.)_ - How would you do this without a window function? _(Looks for a scalar subquery against the third-highest distinct `lifetime_value`, and forces you to articulate why `DENSE_RANK` is cleaner.)_ - This is too slow at a billion views. What do you change first? _(Expects pushing the null filter and aggregation down before the join, or pre-aggregating `content_views` by `content_id` in a rollup so the billion-row scan happens once.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/top_content_by_watch_time) - [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.