ASAmol Shukla

21. Real life: streaming service

Step 1 of 4 · Lesson

Lesson 21 of 21

Real life database practice: streaming service (50 SQL problems)

Practise 50 SQL problems on a 2,000-session streaming database — plans, titles, watch time and completion rates, checked in your browser.

200 min 3 min read 50 problems · 0 solved Streaming Service (2,000 watch sessions)

In short

What is in the streaming practice database?

Four tables: four plans, 400 users, 150 titles and 2,000 watch sessions. A session stores minutes watched, device and whether the title was finished, so watch-time questions aggregate watch_sessions while revenue questions use plans and active users. The database is already selected in every problem.

By the end of this lesson you can

  • Join a four-table streaming schema without duplicating minutes
  • Report watch time per genre, device and month
  • Compute completion rates and plan revenue
  • Rank titles and users with CTEs and window functions

The database you are practising on

Streamflix is a fictional streaming service: four plans, 400 users, 150 titles and 2,000 watch sessions. A session records who watched which title, for how many minutes, on which device, and whether they finished it. Revenue questions combine plan price with the users still active on that plan.

The whole set is fictional but shaped like the real thing: keys, foreign keys, dates, statuses and 2,000 rows in the busiest table. You do not import anything to start — practice mode loads the same rows into your browser. When you want the same data in MySQL Workbench, download the script (4 tables, 20 columns) and run it there.

What one row means

Read the grain before you write a join. Joining two tables without knowing what one row of each means is the single most common cause of doubled totals.

Table One row is Columns you will use
plans one subscription plan plan_id, plan_name, monthly_price, max_streams
users one subscriber user_id, user_name, country, plan_id, signup_date, status
titles one movie or series title_id, title_name, kind, genre, release_year, age_rating
watch_sessions one viewing session session_id, user_id, title_id, watch_date, minutes_watched, device, completed

Your database is already selected

Every problem below runs against this database only. The code arena preselects it and does not show a database dropdown, so you never see MySQL's ERROR 1046: No database selected here.

You write one query, press Check answer, and it is compared row by row with the reference answer — same rows, same columns, any valid query wins. Use Hint first, Compare with the reference answer once you have tried, and Reset database if you changed rows while experimenting.

Common mistakes

Wrong

SUM(minutes_watched) after joining titles and users without checking the grain

Fix

Write down what one row of the result should be, and aggregate before joining anything with many rows per key.

Why: One session has one user and one title, so those joins are safe — but a second one-to-many join would repeat minutes.

Wrong

AVG(completed) treated as a percentage of users

Fix

Say what the denominator is: sessions for a completion rate, distinct users for a user-level rate.

Why: It is a percentage of sessions, and sessions are not evenly spread across users.

Wrong

Counting cancelled users as revenue

Fix

Filter status = 'active' before multiplying plan price by users.

Why: Plan price is only revenue while the subscription is active.

Check yourself

  1. Can you say in one sentence what one row of plans means?
  2. Can you write a query that returns exactly one number from a 2,000-row table without scrolling the result?
  3. Can you explain why two of your joins could double a total, and how you avoided it?

Answer out loud or in your notebook. If one is hard, reread that section before practising.

Frequently asked questions

Do I need to install MySQL and import this data?

No. The code arena loads the streaming database in your browser and preselects it for every problem. Download the .sql script to run the same queries in Workbench.

Are the answers checked automatically?

Yes. Your query and the reference answer run on fresh copies of the same rows and the results are compared row by row, so any correct query is accepted.

Is the streaming data real?

No. It is fictional and generated from a fixed seed so every learner sees the same rows and the same expected answers.

Quick quiz — 3 questions

Quick Check: Real life: streaming service

1 / 3
Why can summing minutes after joining users and plans go wrong?

Answers — Real life: streaming service

  1. Easy. List movies released after 2020. Target: 11 rows · title_id, title_name, release_year · (3, Broken City 3, 2023); (12, Broken Garden 12, 2024) …

    SELECT title_id, title_name, release_year FROM titles WHERE kind = 'movie' AND release_year > 2020 ORDER BY title_id LIMIT 20;

  2. Easy. List active users in India. Target: 20 rows · user_id, user_name, plan_id · (2, Manav Kaur, 1); (3, Sneha Das, 4) …

    SELECT user_id, user_name, plan_id FROM users WHERE status = 'active' AND country = 'India' ORDER BY user_id LIMIT 20;

  3. Easy. List plans from cheapest to most expensive. Target: 4 rows · plan_id, plan_name, monthly_price · (1, Basic, 149); (2, Standard, 299) …

    SELECT plan_id, plan_name, monthly_price FROM plans ORDER BY monthly_price, plan_id;

  4. Easy. Show the first 20 watch sessions on TV. Target: 20 rows · session_id, watch_date, minutes_watched · (1, 2024-09-03, 82); (3, 2025-09-13, 7) …

    SELECT session_id, watch_date, minutes_watched FROM watch_sessions WHERE device = 'tv' ORDER BY session_id LIMIT 20;

  5. Easy. List Drama titles. Target: 19 rows · title_id, title_name · (3, Broken City 3); (12, Broken Garden 12) …

    SELECT title_id, title_name FROM titles WHERE genre = 'Drama' ORDER BY title_id LIMIT 20;

  6. Easy. List users on plan 3. Target: 20 rows · user_id, user_name, status · (8, Diya Chopra, cancelled); (9, Nikhil Reddy, active) …

    SELECT user_id, user_name, status FROM users WHERE plan_id = 3 ORDER BY user_id LIMIT 20;

  7. Easy. Show the 20 longest watch sessions. Target: 20 rows · session_id, minutes_watched, device · (79, 180, tv); (176, 180, tablet) …

    SELECT session_id, minutes_watched, device FROM watch_sessions ORDER BY minutes_watched DESC, session_id LIMIT 20;

  8. Easy. List every genre once. Target: 7 rows · genre · (Action); (Comedy) …

    SELECT DISTINCT genre FROM titles ORDER BY genre;

  9. Easy. Count all watch sessions. Target: 1 row · sessions · (2000)

    SELECT COUNT(*) AS sessions FROM watch_sessions;

  10. Easy. List titles rated A. Target: 20 rows · title_id, title_name, release_year · (5, Silent Story 5, 1996); (7, Golden River 7, 2020) …

    SELECT title_id, title_name, release_year FROM titles WHERE age_rating = 'A' ORDER BY title_id LIMIT 20;

  11. Medium. List movies released between 2010 and 2020. Target: 19 rows · title_id, title_name, release_year · (77, Broken Story 77, 2010); (140, New Promise 140, 2010) …

    SELECT title_id, title_name, release_year FROM titles WHERE kind = 'movie' AND release_year BETWEEN 2010 AND 2020 ORDER BY release_year, title_id LIMIT 20;

  12. Medium. List paused and cancelled users. Target: 20 rows · user_id, user_name, status · (8, Diya Chopra, cancelled); (13, Sneha Bose, cancelled) …

    SELECT user_id, user_name, status FROM users WHERE status IN ('paused', 'cancelled') ORDER BY user_id LIMIT 20;

  13. Medium. List users whose name starts with S. Target: 20 rows · user_id, user_name · (3, Sneha Das); (4, Saanvi Joshi) …

    SELECT user_id, user_name FROM users WHERE user_name LIKE 'S%' ORDER BY user_id LIMIT 20;

  14. Medium. List watch sessions from 2024. Target: 20 rows · session_id, watch_date · (1, 2024-09-03); (5, 2024-05-10) …

    SELECT session_id, watch_date FROM watch_sessions WHERE watch_date >= '2024-01-01' AND watch_date < '2025-01-01' ORDER BY session_id LIMIT 20;

  15. Medium. Show each session's month, first 20 rows. Target: 20 rows · session_id, watch_date, month · (1, 2024-09-03, 2024-09); (2, 2025-02-19, 2025-02) …

    SELECT session_id, watch_date, DATE_FORMAT(watch_date, '%Y-%m') AS month FROM watch_sessions ORDER BY session_id LIMIT 20;

  16. Medium. Label each session as Short, Normal or Long. Target: 20 rows · session_id, minutes_watched, band · (1, 82, Normal); (2, 139, Long) …

    SELECT session_id, minutes_watched, CASE WHEN minutes_watched < 30 THEN 'Short' WHEN minutes_watched < 90 THEN 'Normal' ELSE 'Long' END AS band FROM watch_sessions ORDER BY session_id LIMIT 20;

  17. Medium. List titles whose name contains City. Target: 18 rows · title_id, title_name · (2, Hidden City 2); (3, Broken City 3) …

    SELECT title_id, title_name FROM titles WHERE title_name LIKE '%City%' ORDER BY title_id LIMIT 20;

  18. Medium. List users who signed up during 2025. Target: 20 rows · user_id, user_name, signup_date · (1, Arjun Chopra, 2025-08-09); (5, Kavya Bose, 2025-09-17) …

    SELECT user_id, user_name, signup_date FROM users WHERE signup_date >= '2025-01-01' AND signup_date < '2026-01-01' ORDER BY user_id LIMIT 20;

  19. Medium. Show each country in capitals with the user's name. Target: 20 rows · user_name, country · (Arjun Chopra, SINGAPORE); (Manav Kaur, INDIA) …

    SELECT user_name, UPPER(country) AS country FROM users ORDER BY user_id LIMIT 20;

  20. Medium. Count how many different devices are used. Target: 1 row · devices · (4)

    SELECT COUNT(DISTINCT device) AS devices FROM watch_sessions;

  21. Medium. Show each title and its name length. Target: 20 rows · title_name, name_length · (Silent River 1, 14); (Hidden City 2, 13) …

    SELECT title_name, LENGTH(title_name) AS name_length FROM titles ORDER BY title_id LIMIT 20;

  22. Medium. List unfinished watch sessions. Target: 20 rows · session_id, minutes_watched, device · (3, 7, tv); (6, 21, tablet) …

    SELECT session_id, minutes_watched, device FROM watch_sessions WHERE completed = 0 ORDER BY session_id LIMIT 20;

  23. Hard. Count watch sessions per device. Target: 4 rows · device, sessions · (phone, 535); (laptop, 513) …

    SELECT device, COUNT(*) AS sessions FROM watch_sessions GROUP BY device ORDER BY sessions DESC, device;

  24. Hard. Show the average minutes watched per genre. Target: 7 rows · genre, avg_minutes · (Sci-Fi, 96.7); (Documentary, 95.7) …

    SELECT t.genre, ROUND(AVG(w.minutes_watched), 1) AS avg_minutes FROM watch_sessions w JOIN titles t ON t.title_id = w.title_id GROUP BY t.genre ORDER BY avg_minutes DESC, t.genre;

  25. Hard. Count users per plan. Target: 4 rows · plan_id, users · (2, 155); (1, 117) …

    SELECT plan_id, COUNT(*) AS users FROM users GROUP BY plan_id ORDER BY users DESC, plan_id;

  26. Hard. List titles with more than 20 watch sessions. Target: 3 rows · title_id, title_name, sessions · (136, Hidden Garden 136, 23); (113, Hidden Road 113, 22) …

    SELECT t.title_id, t.title_name, COUNT(*) AS sessions FROM titles t JOIN watch_sessions w ON w.title_id = t.title_id GROUP BY t.title_id, t.title_name HAVING COUNT(*) > 20 ORDER BY sessions DESC, t.title_id LIMIT 20;

  27. Hard. Show the average watch minutes per plan. Target: 4 rows · plan_id, avg_minutes · (4, 99); (3, 94.1) …

    SELECT u.plan_id, ROUND(AVG(w.minutes_watched), 1) AS avg_minutes FROM users u JOIN watch_sessions w ON w.user_id = u.user_id GROUP BY u.plan_id ORDER BY avg_minutes DESC, u.plan_id;

  28. Hard. Show the completion rate per genre. Target: 7 rows · genre, completed_pct · (Documentary, 71); (Action, 69.8) …

    SELECT t.genre, ROUND(100 * SUM(w.completed) / COUNT(*), 1) AS completed_pct FROM watch_sessions w JOIN titles t ON t.title_id = w.title_id GROUP BY t.genre ORDER BY completed_pct DESC, t.genre;

  29. Hard. Count watch sessions per month of 2025. Target: 12 rows · month, sessions · (2025-01, 89); (2025-02, 70) …

    SELECT DATE_FORMAT(watch_date, '%Y-%m') AS month, COUNT(*) AS sessions FROM watch_sessions WHERE watch_date >= '2025-01-01' AND watch_date < '2026-01-01' GROUP BY month ORDER BY month;

  30. Hard. List users with more than 10 watch sessions. Target: 6 rows · user_id, user_name, sessions · (393, Kavya Rao, 13); (92, Varun Singh, 12) …

    SELECT u.user_id, u.user_name, COUNT(*) AS sessions FROM users u JOIN watch_sessions w ON w.user_id = u.user_id GROUP BY u.user_id, u.user_name HAVING COUNT(*) > 10 ORDER BY sessions DESC, u.user_id LIMIT 20;

  31. Hard. List titles that have never been watched. Target: 0 rows · title_id, title_name · 

    SELECT t.title_id, t.title_name FROM titles t LEFT JOIN watch_sessions w ON w.title_id = t.title_id WHERE w.session_id IS NULL ORDER BY t.title_id LIMIT 20;

  32. Hard. Show the average session length per country. Target: 4 rows · country, avg_minutes · (UAE, 99.4); (Singapore, 94.5) …

    SELECT u.country, ROUND(AVG(w.minutes_watched), 1) AS avg_minutes FROM users u JOIN watch_sessions w ON w.user_id = u.user_id GROUP BY u.country ORDER BY avg_minutes DESC, u.country;

  33. Hard. Show genres with an average session above 90 minutes. Target: 7 rows · genre, avg_minutes · (Sci-Fi, 96.7); (Documentary, 95.7) …

    SELECT t.genre, ROUND(AVG(w.minutes_watched), 1) AS avg_minutes FROM watch_sessions w JOIN titles t ON t.title_id = w.title_id GROUP BY t.genre HAVING AVG(w.minutes_watched) > 90 ORDER BY avg_minutes DESC, t.genre;

  34. Hard. Show monthly revenue per plan from active users. Target: 4 rows · plan_name, active_users, monthly_revenue · (Annual, 27, 40473); (Premium, 79, 39421) …

    SELECT p.plan_name, COUNT(*) AS active_users, SUM(p.monthly_price) AS monthly_revenue FROM plans p JOIN users u ON u.plan_id = p.plan_id WHERE u.status = 'active' GROUP BY p.plan_id, p.plan_name ORDER BY monthly_revenue DESC, p.plan_name;

  35. Hard. List users with no watch sessions. Target: 5 rows · user_id, user_name · (14, Gurleen Rao); (49, Rahul Das) …

    SELECT u.user_id, u.user_name FROM users u LEFT JOIN watch_sessions w ON w.user_id = u.user_id WHERE w.session_id IS NULL ORDER BY u.user_id LIMIT 20;

  36. Hard. Compare watch sessions for movies and series. Target: 2 rows · kind, sessions, minutes · (movie, 1116, 104546); (series, 884, 80797)

    SELECT t.kind, COUNT(*) AS sessions, SUM(w.minutes_watched) AS minutes FROM watch_sessions w JOIN titles t ON t.title_id = w.title_id GROUP BY t.kind ORDER BY sessions DESC, t.kind;

  37. Professional. Show the 10 titles with the most minutes watched. Target: 10 rows · title_id, title_name, minutes · (113, Hidden Road 113, 2125); (125, Hidden Season 125, 2104) …

    SELECT t.title_id, t.title_name, SUM(w.minutes_watched) AS minutes FROM watch_sessions w JOIN titles t ON t.title_id = w.title_id GROUP BY t.title_id, t.title_name ORDER BY minutes DESC, t.title_id LIMIT 10;

  38. Professional. Show each genre's share of watched minutes. Target: 7 rows · genre, minutes, pct · (Action, 36713, 19.8); (Thriller, 29946, 16.2) …

    SELECT t.genre, SUM(w.minutes_watched) AS minutes, ROUND(100 * SUM(w.minutes_watched) / (SELECT SUM(minutes_watched) FROM watch_sessions), 1) AS pct FROM watch_sessions w JOIN titles t ON t.title_id = w.title_id GROUP BY t.genre ORDER BY minutes DESC, t.genre;

  39. Professional. Rank titles by minutes watched inside their genre. Target: 20 rows · genre, title_name, minutes, rnk · (Action, Silent Promise 60, 1827, 1); (Action, Silent Season 107, 1813, 2) …

    WITH per AS (SELECT t.genre, t.title_id, t.title_name, SUM(w.minutes_watched) AS minutes, RANK() OVER (PARTITION BY t.genre ORDER BY SUM(w.minutes_watched) DESC, t.title_id) AS rnk FROM watch_sessions w JOIN titles t ON t.title_id = w.title_id GROUP BY t.genre, t.title_id, t.title_name) SELECT genre, title_name, minutes, rnk FROM per ORDER BY genre, rnk LIMIT 20;

  40. Professional. Show 2025 monthly minutes with a running total. Target: 12 rows · month, minutes, running_total · (2025-01, 8803, 8803); (2025-02, 6774, 15577) …

    WITH m AS (SELECT DATE_FORMAT(watch_date, '%Y-%m') AS month, SUM(minutes_watched) AS minutes FROM watch_sessions WHERE watch_date >= '2025-01-01' AND watch_date < '2026-01-01' GROUP BY month) SELECT month, minutes, SUM(minutes) OVER (ORDER BY month) AS running_total FROM m ORDER BY month;

  41. Professional. Show the month-over-month change in sessions for 2025. Target: 12 rows · month, sessions, prev_sessions, change · (2025-01, 89, NULL, NULL); (2025-02, 70, 89, -19) …

    WITH m AS (SELECT DATE_FORMAT(watch_date, '%Y-%m') AS month, COUNT(*) AS sessions FROM watch_sessions WHERE watch_date >= '2025-01-01' AND watch_date < '2026-01-01' GROUP BY month) SELECT month, sessions, LAG(sessions) OVER (ORDER BY month) AS prev_sessions, sessions - LAG(sessions) OVER (ORDER BY month) AS change FROM m ORDER BY month;

  42. Professional. List users who never watched anything. Target: 5 rows · user_id, user_name · (14, Gurleen Rao); (49, Rahul Das) …

    SELECT u.user_id, u.user_name FROM users u WHERE NOT EXISTS (SELECT 1 FROM watch_sessions w WHERE w.user_id = u.user_id) ORDER BY u.user_id LIMIT 20;

  43. Professional. Show each user's longest watch session. Target: 20 rows · user_id, session_id, watch_date, minutes_watched · (1, 97, 2025-03-04, 158); (2, 957, 2024-12-28, 135) …

    WITH r AS (SELECT user_id, session_id, watch_date, minutes_watched, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY minutes_watched DESC, session_id) AS rn FROM watch_sessions) SELECT user_id, session_id, watch_date, minutes_watched FROM r WHERE rn = 1 ORDER BY user_id LIMIT 20;

  44. Professional. List titles watched above their genre's average minutes. Target: 20 rows · title_name, genre, minutes, genre_avg · (Hidden Road 113, Sci-Fi, 2125, 1418.1); (Hidden Season 125, Comedy, 2104, 1325.2) …

    WITH per AS (SELECT t.genre, t.title_id, t.title_name, SUM(w.minutes_watched) AS minutes FROM watch_sessions w JOIN titles t ON t.title_id = w.title_id GROUP BY t.genre, t.title_id, t.title_name), avg_genre AS (SELECT genre, AVG(minutes) AS avg_minutes FROM per GROUP BY genre) SELECT p.title_name, p.genre, p.minutes, ROUND(a.avg_minutes, 1) AS genre_avg FROM per p JOIN avg_genre a ON a.genre = p.genre WHERE p.minutes > a.avg_minutes ORDER BY p.minutes DESC, p.title_id LIMIT 20;

  45. Professional. Build a 2025 monthly dashboard: sessions, minutes and average minutes. Target: 12 rows · month, sessions, minutes, avg_minutes · (2025-01, 89, 8803, 98.9); (2025-02, 70, 6774, 96.8) …

    SELECT DATE_FORMAT(watch_date, '%Y-%m') AS month, COUNT(*) AS sessions, SUM(minutes_watched) AS minutes, ROUND(AVG(minutes_watched), 1) AS avg_minutes FROM watch_sessions WHERE watch_date >= '2025-01-01' AND watch_date < '2026-01-01' GROUP BY month ORDER BY month;

  46. Professional. Show the first watch session of every user. Target: 20 rows · user_id, session_id, watch_date · (1, 444, 2024-02-24); (2, 1094, 2024-02-23) …

    WITH r AS (SELECT user_id, session_id, watch_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY watch_date, session_id) AS rn FROM watch_sessions) SELECT user_id, session_id, watch_date FROM r WHERE rn = 1 ORDER BY user_id LIMIT 20;

  47. Professional. List users who watched more than 15 different titles. Target: 0 rows · user_id, user_name, titles · 

    SELECT u.user_id, u.user_name, COUNT(DISTINCT w.title_id) AS titles FROM users u JOIN watch_sessions w ON w.user_id = u.user_id GROUP BY u.user_id, u.user_name HAVING COUNT(DISTINCT w.title_id) > 15 ORDER BY titles DESC, u.user_id LIMIT 20;

  48. Professional. Show each plan's share of all watch sessions. Target: 4 rows · plan_name, sessions, pct · (Standard, 771, 38.6); (Basic, 575, 28.8) …

    SELECT p.plan_name, COUNT(*) AS sessions, ROUND(100 * COUNT(*) / (SELECT COUNT(*) FROM watch_sessions), 1) AS pct FROM watch_sessions w JOIN users u ON u.user_id = w.user_id JOIN plans p ON p.plan_id = u.plan_id GROUP BY p.plan_id, p.plan_name ORDER BY sessions DESC, p.plan_name;

  49. Professional. Build a country dashboard: users, sessions and minutes watched. Target: 4 rows · country, users, sessions, minutes · (India, 199, 1002, 90366); (Nepal, 68, 379, 35104) …

    SELECT u.country, COUNT(DISTINCT u.user_id) AS users, COUNT(*) AS sessions, SUM(w.minutes_watched) AS minutes FROM users u JOIN watch_sessions w ON w.user_id = u.user_id GROUP BY u.country ORDER BY minutes DESC, u.country;

  50. Professional. Show watch minutes by device and completion status. Target: 8 rows · device, completed, sessions, minutes · (laptop, 0, 165, 5336); (laptop, 1, 348, 40439) …

    SELECT device, completed, COUNT(*) AS sessions, SUM(minutes_watched) AS minutes FROM watch_sessions GROUP BY device, completed ORDER BY device, completed;

Last lesson

Submit your capstone and you have finished the course

Go back to the course page to review your progress or revisit any lesson.

Course overview
Real life: ride-hailing