Google: YouTube Channels Eligible for the Partner Programme

Hard
691.

Google: YouTube Channels Eligible for the Partner Programme

The YouTube creator-support team reviews which channels qualify for monetisation on 2025-06-30. In this exercise a channel qualifies when, as of that date, all three hold:

  • it has at least 1,000 subscribers — the sum of delta over its subscription events dated on or before 2025-06-30;
  • its public videos earned at least 4,000 watch hours (240,000 minutes) from watch logs dated 2024-07-01 to 2025-06-30 inclusive (unlisted and private videos do not count);
  • it has no active strike: no strike with issued_on on or before 2025-06-30 and expires_on after 2025-06-30.

Return channel_name, subscribers and watch_hours (the qualifying minutes divided by 60, rounded down to a whole number) for every qualifying channel, ordered by channel_name.

A practice scenario set at Google: the tables and data are invented, and this is not a question from, or endorsed by, Google.

Tables

Table: Channel

ColumnType
channel_idint
channel_namevarchar

Primary key: channel_id.

One channel; channel_name is unique.

Table: SubscriptionEvent

ColumnType
event_idint
channel_idint
event_datedate
deltaint

Primary key: event_id.

The net change in a channel's subscribers on one day (negative when more people unsubscribed).

Table: Video

ColumnTypeValues
video_idint
channel_idint
visibilityenumpublic, unlisted, private

Primary key: video_id.

One uploaded video and its current visibility.

Table: WatchLog

ColumnType
log_idint
video_idint
watch_datedate
minutesint

Primary key: log_id.

Minutes watched of one video on one day, summed over all viewers.

Table: Strike

ColumnType
strike_idint
channel_idint
issued_ondate
expires_ondate

Primary key: strike_id.

A community-guidelines strike, in force from issued_on until the day before expires_on.

Example

The rows must come back in the order shown.

Channel

channel_idchannel_name
1Tabla Lessons
2Desi Gadget Lab
3Monsoon Treks
4Pixel Photo Walks

SubscriptionEvent

event_idchannel_idevent_datedelta
112024-03-10800
212025-02-01250
312025-07-02-100
422024-11-111200
522025-06-30-200
632025-01-05700
742024-08-013000

Video

video_idchannel_idvisibility
101public
111private
202public
212unlisted
303public
404public

WatchLog

log_idvideo_idwatch_dateminutes
1102024-06-3090000
2102024-07-01150000
3102025-03-1595000
4112025-01-01200000
5202025-06-30240000
6212025-02-0250000
7302025-04-04400000
8402025-05-05300000

Strike

strike_idchannel_idissued_onexpires_on
142025-04-012025-07-01
212024-10-012025-01-01

Output

channel_namesubscriberswatch_hours
Desi Gadget Lab10004000
Tabla Lessons10504083
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.

Google: YouTube Channels Eligible for the Partner Programme

The YouTube creator-support team reviews which channels qualify for monetisation on 2025-06-30.

  • Difficulty: Hard
  • Topics: Joins, Aggregation, Subqueries, Dates
  • Dialect: MySQL
  • Problem: #691

Problem statement

The YouTube creator-support team reviews which channels qualify for monetisation on 2025-06-30. In this exercise a channel qualifies when, as of that date, all three hold:

  • it has at least 1,000 subscribers — the sum of delta over its subscription events dated on or before 2025-06-30;
  • its public videos earned at least 4,000 watch hours (240,000 minutes) from watch logs dated 2024-07-01 to 2025-06-30 inclusive (unlisted and private videos do not count);
  • it has no active strike: no strike with issued_on on or before 2025-06-30 and expires_on after 2025-06-30.

Return channel_name, subscribers and watch_hours (the qualifying minutes divided by 60, rounded down to a whole number) for every qualifying channel, ordered by channel_name.

A practice scenario set at Google: the tables and data are invented, and this is not a question from, or endorsed by, Google.

Tables

Table: Channel

ColumnType
channel_idint
channel_namevarchar

Primary key: channel_id.

One channel; channel_name is unique.

Table: SubscriptionEvent

ColumnType
event_idint
channel_idint
event_datedate
deltaint

Primary key: event_id.

The net change in a channel's subscribers on one day (negative when more people unsubscribed).

Table: Video

ColumnType
video_idint
channel_idint
visibilityenum(public, unlisted, private)

Primary key: video_id.

One uploaded video and its current visibility.

Table: WatchLog

ColumnType
log_idint
video_idint
watch_datedate
minutesint

Primary key: log_id.

Minutes watched of one video on one day, summed over all viewers.

Table: Strike

ColumnType
strike_idint
channel_idint
issued_ondate
expires_ondate

Primary key: strike_id.

A community-guidelines strike, in force from issued_on until the day before expires_on.

Examples

Example 1

Channel

channel_idchannel_name
1Tabla Lessons
2Desi Gadget Lab
3Monsoon Treks
4Pixel Photo Walks

SubscriptionEvent

event_idchannel_idevent_datedelta
112024-03-10800
212025-02-01250
312025-07-02-100
422024-11-111200
522025-06-30-200
632025-01-05700
742024-08-013000

Video

video_idchannel_idvisibility
101public
111private
202public
212unlisted
303public
404public

WatchLog

log_idvideo_idwatch_dateminutes
1102024-06-3090000
2102024-07-01150000
3102025-03-1595000
4112025-01-01200000
5202025-06-30240000
6212025-02-0250000
7302025-04-04400000
8402025-05-05300000

Strike

strike_idchannel_idissued_onexpires_on
142025-04-012025-07-01
212024-10-012025-01-01

Output

channel_namesubscriberswatch_hours
Desi Gadget Lab10004000
Tabla Lessons10504083

How to solve Google: YouTube Channels Eligible for the Partner Programme

The trap is the fan-out. Joining Channel to SubscriptionEvent and to WatchLog in one go produces every subscription event paired with every watch log, so SUM(delta) gets multiplied by the number of logs and SUM(minutes) by the number of events. Each measure must be aggregated on its own first. The subs CTE sums deltas dated on or before the review date (a later unsubscribe does not count yet); the watch CTE joins logs to videos, keeps public videos and the inclusive window 2024-07-01 to 2025-06-30 (exactly 365 days), and sums minutes per channel.

With one row per channel from each CTE, inner joins keep only channels that have both, and the thresholds are plain comparisons — inclusive, so a channel at exactly 1,000 subscribers or 240,000 minutes qualifies. The strike rule is an anti join: NOT EXISTS a strike issued by the date and expiring after it (a strike expiring on 2025-06-30 has already lapsed). mins DIV 60 gives whole hours rounded down.

The alternative computes the three facts as correlated scalar subqueries in a derived table — also fan-out free, because each subquery aggregates alone — and filters outside. Both read each table once per channel at most.

Reference solution (MySQL)

WITH subs AS (
  SELECT channel_id, SUM(delta) AS subscribers
  FROM SubscriptionEvent
  WHERE event_date <= '2025-06-30'
  GROUP BY channel_id
), watch AS (
  SELECT v.channel_id, SUM(w.minutes) AS mins
  FROM WatchLog w
  JOIN Video v ON v.video_id = w.video_id
  WHERE v.visibility = 'public'
    AND w.watch_date BETWEEN '2024-07-01' AND '2025-06-30'
  GROUP BY v.channel_id
)
SELECT c.channel_name, s.subscribers, w.mins DIV 60 AS watch_hours
FROM Channel c
JOIN subs s ON s.channel_id = c.channel_id
JOIN watch w ON w.channel_id = c.channel_id
WHERE s.subscribers >= 1000
  AND w.mins >= 240000
  AND NOT EXISTS (
    SELECT 1 FROM Strike k
    WHERE k.channel_id = c.channel_id
      AND k.issued_on <= '2025-06-30'
      AND k.expires_on > '2025-06-30'
  )
ORDER BY c.channel_name

Another way

SELECT channel_name, subscribers, FLOOR(mins / 60) AS watch_hours FROM (SELECT c.channel_id, c.channel_name, (SELECT COALESCE(SUM(delta), 0) FROM SubscriptionEvent e WHERE e.channel_id = c.channel_id AND e.event_date <= '2025-06-30') AS subscribers, (SELECT COALESCE(SUM(w.minutes), 0) FROM WatchLog w JOIN Video v ON v.video_id = w.video_id WHERE v.channel_id = c.channel_id AND v.visibility = 'public' AND w.watch_date >= '2024-07-01' AND w.watch_date < '2025-07-01') AS mins, (SELECT COUNT(*) FROM Strike k WHERE k.channel_id = c.channel_id AND '2025-06-30' >= k.issued_on AND '2025-06-30' < k.expires_on) AS active_strikes FROM Channel c) t WHERE subscribers >= 1000 AND mins >= 240000 AND active_strikes = 0 ORDER BY channel_name

← Google: Meet Calls Where Guests Waited for a Late Host · Google: Play Service Fee and the Purchase That Crossed the Tier →