Netflix-style Company ChallengeHardVerified answerSQLite live

Top Country per Genre

Return genre, country, and watch_count (number of sessions) for the top country in each genre, ordered by genre.

  • Window functions
  • Joins
  • Subqueries
  • Aggregation
  • Filtering

Challenge brief

Understand the request

Regional Content Strategy The regional team wants to know which country dominates viewership for each genre to guide localisation investment.

Return genre, country, watch_count for one deterministic country leader per genre.

Return

  • genre
  • country
  • watch_count

Constraints

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

Data you will use

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

watch_history

  • user_idINTEGER
  • movie_idINTEGER

movies

  • movie_idINTEGER
  • genreVARCHAR(50)

users

  • user_idINTEGER
  • countryVARCHAR(50)

Hints, when you need them

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

Hint 1

Count sessions at genre-country grain before choosing leaders.

Hint 2

Order countries within each genre by count and a unique country tie-break.

Hint 3

Retain one row per genre and apply the report order.

Verified SQL answer

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

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

Why this works

Session counts are calculated at genre-country grain, then countries are ranked within each genre with country code as a stable tie-break.

Success check

Returns the complete deterministic result for top country per genre

Expected result

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

genrecountrywatch_count
ActionUS1
CrimeCA1
DramaUK1
Sci-FiIN2

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.