Netflix SQL Interview Questions


  1. Write a query to find the number of unique viewers for each movie.
SELECT movie_id, COUNT(DISTINCT user_id) AS num_unique_viewers
FROM view_history
GROUP BY movie_id;
  1. Write a query to find the top 100 genres with the highest average rating for movies released in the last year.
SELECT genre, AVG(rating) AS avg_rating
FROM movie_ratings
JOIN movies ON movie_ratings.movie_id = movies.movie_id
WHERE release_date >= DATE_SUB(NOW(), INTERVAL 1 YEAR)
GROUP BY genre
ORDER BY avg_rating DESC
LIMIT 100;
  1. Write a query to find the average number of hours watched per day for each user in the last month.
SELECT user_id, SUM(duration) / COUNT(DISTINCT DATE(view_time)) / 3600 AS avg_hours_watched_per_day
FROM view_history
WHERE view_time >= DATE_SUB(NOW(), INTERVAL 1 MONTH)
GROUP BY user_id;
  1. Write a query to find the top 5 directors with the highest number of movies available on the platform.
SELECT director, COUNT(*) AS num_movies
FROM movies
GROUP BY director
ORDER BY num_movies DESC
LIMIT 5;
  1. Write a query to find the number of users who have watched more than 100 hours of content in the last quarter.
SELECT COUNT(DISTINCT user_id) AS num_high_watchers
FROM (
  SELECT user_id, SUM(duration) AS total_duration
  FROM view_history
  WHERE view_time >= DATE_SUB(NOW(), INTERVAL 3 MONTH)
  GROUP BY user_id
  HAVING total_duration > 360000 -- 100 hours in seconds
) AS temp;
  1. Write a query to find the top 50 actors who have starred in the most popular movies of all time (as determined by number of views).
SELECT actor, COUNT(*) AS num_popular_movies
FROM movies
JOIN view_history ON movies.movie_id = view_history.movie_id
WHERE view_history.view_time >= DATE_SUB(NOW(), INTERVAL 1 YEAR)
GROUP BY actor
ORDER BY num_popular_movies DESC
LIMIT 50;
  1. Write a query to find the average completion rate for each TV series season (i.e. the percentage of users who watched every episode in the season).
SELECT season_number, 100.0 * COUNT(DISTINCT user_id) / COUNT(DISTINCT CASE WHEN num_episodes_watched = total_episodes THEN user_id END) AS avg_completion_rate
FROM (
  SELECT tv_series_id, season_number, user_id, COUNT(*) AS num_episodes_watched, MAX(episode_number) AS total_episodes
  FROM view_history
  JOIN episodes ON view_history.episode_id = episodes.episode_id
  WHERE episodes.tv_series_id IS NOT NULL
  GROUP BY tv_series_id, season_number, user_id
) AS temp
GROUP BY season_number;
  1. Write a query to find the top 5 countries with the highest number of subscribers.
SELECT country, COUNT(*) AS num_subscribers
FROM subscriptions
GROUP BY country
ORDER BY num_subscribers DESC
LIMIT 5;
  1. Write a query to find the top 10 most-watched movies of the last year.
SELECT movie_title, COUNT(*) AS num_views
FROM view_history
JOIN movies ON view_history.movie_id = movies.movie_id
WHERE view_time >= DATE_SUB(NOW(), INTERVAL 1 YEAR)
GROUP BY movie_title
ORDER BY num_views DESC
LIMIT 10;

Thanks For Reading..