Titles Licensed in Every Region on Launch Day

Medium
487.

Titles Licensed in Every Region on Launch Day

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

ColumnType
title_idint
title_namevarchar

Primary key: title_id.

One row per title in the global catalogue.

Table: Region

ColumnType
region_codechar
region_namevarchar

Primary key: region_code.

Every region the service launches in.

Table: Licence

ColumnType
licence_idint
title_idint
region_codechar
starts_ondate
ends_ondate

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.

Example

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

Title

title_idtitle_name
1Monsoon Diaries
2Neon Bazaar
3The Quiet Coast
4Gully Kings

Region

region_coderegion_name
INIndia
AEUAE
SGSingapore

Licence

licence_idtitle_idregion_codestarts_onends_on
11IN2023-01-01NULL
21AE2024-01-012024-12-31
31SG2024-07-012025-06-30
42IN2023-05-01NULL
52AE2023-05-012024-06-30
62SG2023-05-01NULL
73IN2024-01-012024-07-01
83IN2024-07-01NULL
93AE2022-01-01NULL
103SG2024-03-012024-09-30
1 more row

Output

title_name
Monsoon Diaries
The Quiet Coast
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.

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

ColumnType
title_idint
title_namevarchar

Primary key: title_id.

One row per title in the global catalogue.

Table: Region

ColumnType
region_codechar
region_namevarchar

Primary key: region_code.

Every region the service launches in.

Table: Licence

ColumnType
licence_idint
title_idint
region_codechar
starts_ondate
ends_ondate

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_idtitle_name
1Monsoon Diaries
2Neon Bazaar
3The Quiet Coast
4Gully Kings

Region

region_coderegion_name
INIndia
AEUAE
SGSingapore

Licence

licence_idtitle_idregion_codestarts_onends_on
11IN2023-01-01NULL
21AE2024-01-012024-12-31
31SG2024-07-012025-06-30
42IN2023-05-01NULL
52AE2023-05-012024-06-30
62SG2023-05-01NULL
73IN2024-01-012024-07-01
83IN2024-07-01NULL
93AE2022-01-01NULL
103SG2024-03-012024-09-30
114IN2024-01-01NULL

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 →