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
deltaover 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_onon or before 2025-06-30 andexpires_onafter 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
| Column | Type |
|---|---|
| channel_id | int |
| channel_name | varchar |
Primary key: channel_id.
One channel; channel_name is unique.
Table: SubscriptionEvent
| Column | Type |
|---|---|
| event_id | int |
| channel_id | int |
| event_date | date |
| delta | int |
Primary key: event_id.
The net change in a channel's subscribers on one day (negative when more people unsubscribed).
Table: Video
| Column | Type |
|---|---|
| video_id | int |
| channel_id | int |
| visibility | enum(public, unlisted, private) |
Primary key: video_id.
One uploaded video and its current visibility.
Table: WatchLog
| Column | Type |
|---|---|
| log_id | int |
| video_id | int |
| watch_date | date |
| minutes | int |
Primary key: log_id.
Minutes watched of one video on one day, summed over all viewers.
Table: Strike
| Column | Type |
|---|---|
| strike_id | int |
| channel_id | int |
| issued_on | date |
| expires_on | date |
Primary key: strike_id.
A community-guidelines strike, in force from issued_on until the day before expires_on.
Examples
Example 1
Channel
| channel_id | channel_name |
|---|---|
| 1 | Tabla Lessons |
| 2 | Desi Gadget Lab |
| 3 | Monsoon Treks |
| 4 | Pixel Photo Walks |
SubscriptionEvent
| event_id | channel_id | event_date | delta |
|---|---|---|---|
| 1 | 1 | 2024-03-10 | 800 |
| 2 | 1 | 2025-02-01 | 250 |
| 3 | 1 | 2025-07-02 | -100 |
| 4 | 2 | 2024-11-11 | 1200 |
| 5 | 2 | 2025-06-30 | -200 |
| 6 | 3 | 2025-01-05 | 700 |
| 7 | 4 | 2024-08-01 | 3000 |
Video
| video_id | channel_id | visibility |
|---|---|---|
| 10 | 1 | public |
| 11 | 1 | private |
| 20 | 2 | public |
| 21 | 2 | unlisted |
| 30 | 3 | public |
| 40 | 4 | public |
WatchLog
| log_id | video_id | watch_date | minutes |
|---|---|---|---|
| 1 | 10 | 2024-06-30 | 90000 |
| 2 | 10 | 2024-07-01 | 150000 |
| 3 | 10 | 2025-03-15 | 95000 |
| 4 | 11 | 2025-01-01 | 200000 |
| 5 | 20 | 2025-06-30 | 240000 |
| 6 | 21 | 2025-02-02 | 50000 |
| 7 | 30 | 2025-04-04 | 400000 |
| 8 | 40 | 2025-05-05 | 300000 |
Strike
| strike_id | channel_id | issued_on | expires_on |
|---|---|---|---|
| 1 | 4 | 2025-04-01 | 2025-07-01 |
| 2 | 1 | 2024-10-01 | 2025-01-01 |
Output
| channel_name | subscribers | watch_hours |
|---|---|---|
| Desi Gadget Lab | 1000 | 4000 |
| Tabla Lessons | 1050 | 4083 |
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_nameAnother 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 →