Google-style Company ChallengeHardVerified answerSQLite live

Top Viewer per Video Category

In each video category, which user has watched the most total seconds of content?

  • Window functions
  • Joins
  • Subqueries
  • Aggregation
  • Filtering

Challenge brief

Understand the request

Video Personalisation is building category-affinity user profiles and needs the single most-engaged user in each video category.

Return category, user_id, total_watch in the declared deterministic order.

Return

  • category
  • user_id
  • total_watch (total watch seconds for that user in that category)

Constraints

  • Return exactly one highest-watch-time user per video category
  • Break equal watch totals by smaller user ID
  • Order final rows by watch time descending, then category

Data you will use

Review the relevant tables before deciding how to join, filter, or aggregate them.

video_views

  • user_idINTEGER
  • video_idINTEGER
  • watch_timeINTEGER

videos

  • video_idINTEGER
  • categoryVARCHAR(50)

Hints, when you need them

Open one clue at a time so you still do the reasoning.

Hint 1

First calculate category-and-user watch totals.

Hint 2

Within each category, order those totals with a unique user tie-break.

Hint 3

Retain one leading row per category and apply the final report order.

Verified SQL answer

Attempt the problem first, then compare structure and reasoning—not just syntax.

Reveal solution and explanation
WITH category_watch AS (SELECT v.category, vv.user_id, SUM(vv.watch_time) AS total_watch FROM video_views vv INNER JOIN videos v ON vv.video_id = v.video_id GROUP BY v.category, vv.user_id), ranked_viewers AS (SELECT category, user_id, total_watch, ROW_NUMBER() OVER (PARTITION BY category ORDER BY total_watch DESC, user_id) AS rn FROM category_watch) SELECT category, user_id, total_watch FROM ranked_viewers WHERE rn = 1 ORDER BY total_watch DESC, category

Why this works

ROW_NUMBER() resets to 1 for each category's top watcher. User 2 watched Python Tutorial (600s) + SQL Questions (480s) + Python again (700s) + Web Dev (720s) = 2500s of Education. WHERE rn=1 picks exactly one per category.

Success check

Returns the complete deterministic result for top viewer per video category

Expected result

Use this output to verify values, aliases, ordering, and row count.

categoryuser_idtotal_watch
Education22500
Gaming81320
Finance9860
Technology18620
Entertainment1520
Travel17480
Health14390
Food10310

Learn the concepts behind this answer

Strengthen your understanding with these targeted learning topics:

Continue practicing

Explore related company challenges

SQL Interview Practice →

Return to the complete interview preparation experience.