Data Analyst Interview Questions & Answers: Set 16, Streaming & Media

SQL

1. 1.Calculate average watch time per user per day for each content category.

   Hide answer

   Answer: First total each user's watch time per day per category, then average those user-day totals by category. Tables assumed: views(user_id, title_id, start_ts, watch_seconds) and titles(title_id, category). Trap: AVG(watch_seconds) on raw rows gives the average per session, not per user per day, so aggregate in two steps. Note this averages only over days the user actually watched in that category; if you need all active days, build a user-day spine first.

   WITH daily AS (
     SELECT v.user_id, t.category,
            v.start_ts::date AS view_date,
            SUM(v.watch_seconds) / 60.0 AS minutes
     FROM views v
     JOIN titles t ON t.title_id = v.title_id
     GROUP BY v.user_id, t.category, v.start_ts::date
   )
   SELECT category,
          ROUND(AVG(minutes), 1) AS avg_min_per_user_day
   FROM daily
   GROUP BY category
   ORDER BY avg_min_per_user_day DESC;

2. 2.Find shows where more than 50% of viewers stopped before episode 2.

   Hide answer

   Answer: Build one row per show per user with two flags: did they watch episode 1, and did they watch any later episode. Among episode-1 viewers, the share with no later episode is the stop rate; keep shows where it is above 50%. Table assumed: episode_views(user_id, show_id, episode_number). Using 1.0 avoids integer division. In practice also require a minimum number of viewers so tiny shows do not appear just by noise.

   WITH per_user AS (
     SELECT show_id, user_id,
       MAX(CASE WHEN episode_number = 1 THEN 1 ELSE 0 END) AS saw_ep1,
       MAX(CASE WHEN episode_number >= 2 THEN 1 ELSE 0 END) AS saw_later
     FROM episode_views
     GROUP BY show_id, user_id
   )
   SELECT show_id,
          COUNT(*) AS ep1_viewers,
          ROUND(100.0 * AVG(1.0 - saw_later), 1) AS pct_stopped
   FROM per_user
   WHERE saw_ep1 = 1
   GROUP BY show_id
   HAVING AVG(1.0 - saw_later) > 0.5;