Monthly Active Viewers and Minutes per Viewer

Medium
481.

Monthly Active Viewers and Minutes per Viewer

MediumDatesAggregation

MySQL

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

ColumnType
session_idint
profile_idint
started_atdatetime
watched_minutesint

Primary key: session_id.

One row per playback session; a session belongs to the month in which it started.

Example

The rows must come back in the order shown.

WatchSession

session_idprofile_idstarted_atwatched_minutes
112024-01-05 20:15:0045
222024-01-20 22:40:001
312024-01-31 23:58:0030
432024-02-01 00:10:0050
512024-02-14 19:00:0020
632024-02-15 21:30:002
722024-03-02 08:00:001

Output

monthactive_profilesminutes_per_profile
2024-01175
2024-02236
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
session_idint
profile_idint
started_atdatetime
watched_minutesint

Primary key: session_id.

One row per playback session; a session belongs to the month in which it started.

Examples

Example 1

WatchSession

session_idprofile_idstarted_atwatched_minutes
112024-01-05 20:15:0045
222024-01-20 22:40:001
312024-01-31 23:58:0030
432024-02-01 00:10:0050
512024-02-14 19:00:0020
632024-02-15 21:30:002
722024-03-02 08:00:001

Output

monthactive_profilesminutes_per_profile
2024-01175
2024-02236

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 month

Another 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 month

Another 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 →