Profiles With No Streams Since the New Year

Easy
474.

Profiles With No Streams Since the New Year

EasyJoins

MySQL

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

ColumnType
profile_idint
account_idint
profile_namevarchar

Primary key: profile_id.

One row per viewer profile; an account can hold several.

Table: WatchSession

ColumnType
session_idint
profile_idint
title_idint
started_atdatetime
watched_minutesint

Primary key: session_id.

One row per playback session; profile_id always names a row of Profile.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

Profile

profile_idaccount_idprofile_name
110Aarav
210Kids
311Meera
412Rohan
512Diya

WatchSession

session_idprofile_idtitle_idstarted_atwatched_minutes
1172024-02-03 21:10:0048
2232023-12-31 23:50:0030
3352023-11-14 20:00:0095
4352024-01-01 00:05:0012
5522023-08-19 22:30:0041

Output

profile_idprofile_name
2Kids
4Rohan
5Diya
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.

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

ColumnType
profile_idint
account_idint
profile_namevarchar

Primary key: profile_id.

One row per viewer profile; an account can hold several.

Table: WatchSession

ColumnType
session_idint
profile_idint
title_idint
started_atdatetime
watched_minutesint

Primary key: session_id.

One row per playback session; profile_id always names a row of Profile.

Examples

Example 1

Profile

profile_idaccount_idprofile_name
110Aarav
210Kids
311Meera
412Rohan
512Diya

WatchSession

session_idprofile_idtitle_idstarted_atwatched_minutes
1172024-02-03 21:10:0048
2232023-12-31 23:50:0030
3352023-11-14 20:00:0095
4352024-01-01 00:05:0012
5522023-08-19 22:30:0041

Output

profile_idprofile_name
2Kids
4Rohan
5Diya

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 NULL

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