Titles Licensed in Every Region on Launch Day — SQL Problem
Marketing plans a global push on 2024-07-01 and can only feature titles that are streamable in every region listed in Region that day.
- Difficulty: Medium
- Topics: Subqueries, Aggregation, Dates
- Dialect: MySQL
- Problem: #487
Problem statement
Marketing plans a global push on 2024-07-01 and can only feature titles that are streamable in every region listed in Region that day. A licence covers a region from starts_on to ends_on, both included; ends_on NULL means the licence has no end date. A title can hold several licences for the same region.
Return the column title_name for every qualifying title, in any order.
Tables
Table: Title
| Column | Type |
|---|---|
| title_id | int |
| title_name | varchar |
Primary key: title_id.
One row per title in the global catalogue.
Table: Region
| Column | Type |
|---|---|
| region_code | char |
| region_name | varchar |
Primary key: region_code.
Every region the service launches in.
Table: Licence
| Column | Type |
|---|---|
| licence_id | int |
| title_id | int |
| region_code | char |
| starts_on | date |
| ends_on | date |
Primary key: licence_id.
A window during which a title may be streamed in a region. ends_on is NULL for an open-ended licence.
Examples
Example 1
Title
| title_id | title_name |
|---|---|
| 1 | Monsoon Diaries |
| 2 | Neon Bazaar |
| 3 | The Quiet Coast |
| 4 | Gully Kings |
Region
| region_code | region_name |
|---|---|
| IN | India |
| AE | UAE |
| SG | Singapore |
Licence
| licence_id | title_id | region_code | starts_on | ends_on |
|---|---|---|---|---|
| 1 | 1 | IN | 2023-01-01 | NULL |
| 2 | 1 | AE | 2024-01-01 | 2024-12-31 |
| 3 | 1 | SG | 2024-07-01 | 2025-06-30 |
| 4 | 2 | IN | 2023-05-01 | NULL |
| 5 | 2 | AE | 2023-05-01 | 2024-06-30 |
| 6 | 2 | SG | 2023-05-01 | NULL |
| 7 | 3 | IN | 2024-01-01 | 2024-07-01 |
| 8 | 3 | IN | 2024-07-01 | NULL |
| 9 | 3 | AE | 2022-01-01 | NULL |
| 10 | 3 | SG | 2024-03-01 | 2024-09-30 |
| 11 | 4 | IN | 2024-01-01 | NULL |
Output
| title_name |
|---|
| Monsoon Diaries |
| The Quiet Coast |
How to solve Titles Licensed in Every Region on Launch Day
This is relational division: titles related to all rows of another table. The counting form filters licences to the ones live on the launch day — starts_on <= '2024-07-01' and either no end date or ends_on >= '2024-07-01' — groups by title, and keeps the titles whose number of distinct live regions equals the total number of regions, which a scalar subquery supplies.
Two details matter. ends_on NULL means "no end", and NULL >= date is never true, so the open-ended case needs its own IS NULL test (or COALESCE with a far-future date). And a title can have two live licences for India; COUNT(DISTINCT region_code) stops that from making up for a missing region.
The double NOT EXISTS reads as the definition: keep the title if there is no region for which no live licence exists. It never counts, so duplicates are harmless. Both forms are linear in the licences with indexes on (title_id, region_code).
Reference solution (MySQL)
SELECT t.title_name
FROM Title t
JOIN Licence l ON l.title_id = t.title_id
WHERE l.starts_on <= '2024-07-01'
AND (l.ends_on IS NULL OR l.ends_on >= '2024-07-01')
GROUP BY t.title_id, t.title_name
HAVING COUNT(DISTINCT l.region_code) = (SELECT COUNT(*) FROM Region)Another way
SELECT t.title_name FROM Title t
WHERE NOT EXISTS (
SELECT 1 FROM Region r WHERE NOT EXISTS (
SELECT 1 FROM Licence l WHERE l.title_id = t.title_id AND l.region_code = r.region_code
AND '2024-07-01' BETWEEN l.starts_on AND COALESCE(l.ends_on, '9999-12-31')))← Listeners With a Song on Repeat · Playlist Length as Clock Time →