Monthly Active Viewers and Minutes per Viewer — SQL Problem
The monthly board deck shows engagement. A session shorter than 2 minutes is an accidental autoplay and is ignored completely.
- Difficulty: Medium
- Topics: Dates, Aggregation
- Dialect: MySQL
- Problem: #481
Problem statement
The monthly board deck shows engagement. A session shorter than 2 minutes is an accidental autoplay and is ignored completely. Among the remaining sessions, a profile is active in a month when it has at least one session starting in that month.
Return one row per month that has any counted session, with the columns month (as YYYY-MM), active_profiles (distinct profiles) and minutes_per_profile (total counted minutes divided by active_profiles, rounded to 2 decimal places). Order the rows by month.
Tables
Table: WatchSession
| Column | Type |
|---|---|
| session_id | int |
| profile_id | int |
| started_at | datetime |
| watched_minutes | int |
Primary key: session_id.
One row per playback session; a session belongs to the month in which it started.
Examples
Example 1
WatchSession
| session_id | profile_id | started_at | watched_minutes |
|---|---|---|---|
| 1 | 1 | 2024-01-05 20:15:00 | 45 |
| 2 | 2 | 2024-01-20 22:40:00 | 1 |
| 3 | 1 | 2024-01-31 23:58:00 | 30 |
| 4 | 3 | 2024-02-01 00:10:00 | 50 |
| 5 | 1 | 2024-02-14 19:00:00 | 20 |
| 6 | 3 | 2024-02-15 21:30:00 | 2 |
| 7 | 2 | 2024-03-02 08:00:00 | 1 |
Output
| month | active_profiles | minutes_per_profile |
|---|---|---|
| 2024-01 | 1 | 75 |
| 2024-02 | 2 | 36 |
How to solve Monthly Active Viewers and Minutes per Viewer
Bucket each session by the month it started in — DATE_FORMAT(started_at, '%Y-%m') gives 2024-01, which also sorts correctly as text — and group by that bucket. The autoplay rule removes sessions before grouping, in WHERE, so a profile whose only January session lasted one minute is not active in January at all.
Inside a month, COUNT(DISTINCT profile_id) counts viewers rather than sessions, and SUM(watched_minutes) totals the counted minutes; their ratio, rounded to two places, is the minutes per active profile. Note that a session at 23:58 on the 31st belongs to January even if it ran past midnight.
The alternative pre-aggregates per (month, profile) and then counts rows, which avoids DISTINCT; another builds the label from YEAR and MONTH with LPAD. A month with only autoplay sessions produces no row. The query is one scan plus a sort by month.
Reference solution (MySQL)
SELECT DATE_FORMAT(started_at, '%Y-%m') AS month,
COUNT(DISTINCT profile_id) AS active_profiles,
ROUND(SUM(watched_minutes) / COUNT(DISTINCT profile_id), 2) AS minutes_per_profile
FROM WatchSession
WHERE watched_minutes >= 2
GROUP BY DATE_FORMAT(started_at, '%Y-%m')
ORDER BY monthAnother way
SELECT month, COUNT(*) AS active_profiles, ROUND(SUM(mins) / COUNT(*), 2) AS minutes_per_profile
FROM (SELECT LEFT(started_at, 7) AS month, profile_id, SUM(watched_minutes) AS mins FROM WatchSession WHERE watched_minutes > 1 GROUP BY LEFT(started_at, 7), profile_id) p
GROUP BY month ORDER BY monthAnother way
SELECT month, COUNT(DISTINCT profile_id) AS active_profiles, ROUND(SUM(watched_minutes) / COUNT(DISTINCT profile_id), 2) AS minutes_per_profile
FROM (SELECT CONCAT(YEAR(started_at), '-', LPAD(MONTH(started_at), 2, '0')) AS month, profile_id, watched_minutes FROM WatchSession WHERE watched_minutes >= 2) s
GROUP BY month ORDER BY month← Movies Longer Than the Average Movie · Completion Rate of Every Series →