Profiles With No Streams Since the New Year — SQL Problem
The growth team wants to nudge profiles that have gone quiet.
- Difficulty: Easy
- Topics: Joins
- Dialect: MySQL
- Problem: #474
Problem statement
The growth team wants to nudge profiles that have gone quiet. A profile is quiet when it has no watch session starting on or after 2024-01-01 — sessions from 2023 do not count, and a profile that has never streamed at all is quiet too.
Return the columns profile_id and profile_name of every quiet profile, in any order.
Tables
Table: Profile
| Column | Type |
|---|---|
| profile_id | int |
| account_id | int |
| profile_name | varchar |
Primary key: profile_id.
One row per viewer profile; an account can hold several.
Table: WatchSession
| Column | Type |
|---|---|
| session_id | int |
| profile_id | int |
| title_id | int |
| started_at | datetime |
| watched_minutes | int |
Primary key: session_id.
One row per playback session; profile_id always names a row of Profile.
Examples
Example 1
Profile
| profile_id | account_id | profile_name |
|---|---|---|
| 1 | 10 | Aarav |
| 2 | 10 | Kids |
| 3 | 11 | Meera |
| 4 | 12 | Rohan |
| 5 | 12 | Diya |
WatchSession
| session_id | profile_id | title_id | started_at | watched_minutes |
|---|---|---|---|---|
| 1 | 1 | 7 | 2024-02-03 21:10:00 | 48 |
| 2 | 2 | 3 | 2023-12-31 23:50:00 | 30 |
| 3 | 3 | 5 | 2023-11-14 20:00:00 | 95 |
| 4 | 3 | 5 | 2024-01-01 00:05:00 | 12 |
| 5 | 5 | 2 | 2023-08-19 22:30:00 | 41 |
Output
| profile_id | profile_name |
|---|---|
| 2 | Kids |
| 4 | Rohan |
| 5 | Diya |
How to solve Profiles With No Streams Since the New Year
This is an anti join with a twist: only recent sessions count as a partner. LEFT JOIN Profile to WatchSession with both conditions in the ON clause — the profile matches and the session started on or after midnight on 2024-01-01. A profile with a recent session gets matched rows; a profile with only 2023 sessions, or none, gets one row with NULLs, and WHERE w.session_id IS NULL keeps exactly those.
Putting the date test in WHERE instead is the classic mistake: the profile with only old sessions is then matched to its 2023 rows, those rows are not NULL, the date filter throws them away — and the profile disappears from the answer although it is quiet.
Because started_at is a fixed-format datetime, comparing it to the text '2024-01-01' works: a session at 2023-12-31 23:50 is smaller, 2024-01-01 00:05 is larger. NOT EXISTS states the rule directly, and NOT IN is safe here because profile_id is never NULL in WatchSession. Each form is one index probe per profile.
Reference solution (MySQL)
SELECT p.profile_id, p.profile_name
FROM Profile p
LEFT JOIN WatchSession w
ON w.profile_id = p.profile_id AND w.started_at >= '2024-01-01'
WHERE w.session_id IS NULLAnother way
SELECT profile_id, profile_name FROM Profile p WHERE NOT EXISTS (SELECT 1 FROM WatchSession w WHERE w.profile_id = p.profile_id AND w.started_at >= '2024-01-01 00:00:00')Another way
SELECT profile_id, profile_name FROM Profile WHERE profile_id NOT IN (SELECT profile_id FROM WatchSession WHERE YEAR(started_at) >= 2024)← New Kids-Safe Originals for the Family Row · Watch Hours by Genre →