Netflix-style Company ChallengeHardVerified answerSQLite live

Top Genre per Plan

Return plan, top_genre, and watch_count for the most-watched genre within each subscription plan, ordered by plan.

  • Window functions
  • Joins
  • Subqueries
  • Aggregation
  • Filtering

Challenge brief

Understand the request

Plan-Based Recommendations The personalisation team wants to seed plan-specific genre recommendations and needs to know which genre each plan's subscribers watch most.

Return one genre leader per plan and order the result by plan.

Return

  • plan
  • top_genre
  • watch_count

Constraints

  • Count identified watch sessions per plan and genre
  • Return one leading genre per plan
  • Break equal session counts alphabetically by genre
  • Order by plan

Data you will use

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

subscriptions

  • user_idINTEGER
  • planVARCHAR(20)

watch_history

  • user_idINTEGER
  • movie_idINTEGER

movies

  • movie_idINTEGER
  • genreVARCHAR(50)

Hints, when you need them

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

Hint 1

Count watch sessions at plan-genre grain.

Hint 2

Within each plan, order genre totals with genre name as the tie-break.

Hint 3

Retain one leading genre per plan.

Verified SQL answer

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

Reveal solution and explanation
WITH plan_genre AS (SELECT s.plan, m.genre, COUNT(*) AS watch_count FROM subscriptions s INNER JOIN watch_history w ON s.user_id = w.user_id INNER JOIN movies m ON w.movie_id = m.movie_id GROUP BY s.plan, m.genre), ranked AS (SELECT plan, genre, watch_count, ROW_NUMBER() OVER (PARTITION BY plan ORDER BY watch_count DESC, genre) AS rn FROM plan_genre) SELECT plan, genre AS top_genre, watch_count FROM ranked WHERE rn = 1 ORDER BY plan

Why this works

Watch sessions are counted at plan-genre grain. Genre name provides a stable tie-break when several genres share the maximum for a plan.

Success check

Returns the complete deterministic result for top genre per plan

Expected result

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

plantop_genrewatch_count
BasicSci-Fi2
PremiumCrime2
StandardSci-Fi2

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.