Netflix-style Company ChallengeHardVerified answerSQLite live

Above-Average Viewers

Return user_id and total_minutes for users whose total watch time is above the average total watch time per user, ordered by total_minutes descending.

  • Joins
  • Subqueries
  • Aggregation
  • Filtering
  • Sorting

Challenge brief

Understand the request

Audience Insights The audience insights team defines 'power viewers' as users whose total watch time beats the average and wants this segment for an ad-free promotion.

Find users whose total watch time exceeds the average across all watching users.

Return

  • user_id
  • total_minutes

Constraints

  • Calculate total minutes per identified viewer
  • Compare viewer totals with the average of those viewer totals
  • Order by total minutes descending, then user ID

Data you will use

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

watch_history

  • user_idINTEGER
  • watch_minutesINTEGER

Hints, when you need them

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

Hint 1

First reduce watch facts to one total per identified viewer.

Hint 2

Calculate the average of those totals in a separate stage.

Hint 3

Compare each viewer total with that benchmark and order the result.

Verified SQL answer

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

Reveal solution and explanation
WITH user_totals AS (SELECT user_id, SUM(watch_minutes) AS total_minutes FROM watch_history WHERE user_id IS NOT NULL GROUP BY user_id), avg_total AS (SELECT AVG(total_minutes) AS avg_minutes FROM user_totals) SELECT ut.user_id, ut.total_minutes FROM user_totals ut CROSS JOIN avg_total a WHERE ut.total_minutes > a.avg_minutes ORDER BY ut.total_minutes DESC, ut.user_id

Why this works

The first CTE computes each user's total watch minutes. The second CTE computes the average of those totals (503.75). The main query filters to users above that average. Separating the two steps in CTEs prevents the common mistake of comparing per-session minutes to per-user totals.

Success check

Returns the complete deterministic result for above-average viewers

Expected result

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

user_idtotal_minutes
1800
2650
3600
6550

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.