Creators Booked by Two Rival Brands at Once

Hard
297.

Creators Booked by Two Rival Brands at Once

Creator contracts carry category exclusivity: a creator must not promote two different brands in the same category during overlapping periods. Periods run from start_date to end_date inclusive, so a contract ending on the day another starts overlaps it by one day. Two contracts with the same brand never conflict.

Return every conflicting pair once, with creator_handle, contract_a (the smaller contract_id), contract_b (the larger) and overlap_days (the number of days both run). Order by creator_handle, then contract_a, then contract_b.

Tables

Table: CreatorContract

ColumnTypeValues
contract_idint
creator_handlevarchar
brandvarchar
categoryenumaudio, skincare, footwear
start_datedate
end_datedate

Primary key: contract_id.

One row per signed contract; end_date is never before start_date. Handles and brands are stored in one consistent case.

Example

The rows must come back in the order shown.

CreatorContract

contract_idcreator_handlebrandcategorystart_dateend_date
1@techbyrohanSonicbayaudio2024-03-012024-04-30
2@techbyrohanWavelyaudio2024-04-202024-05-31
3@techbyrohanGlowvedaskincare2024-04-012024-04-30
4@skincarebyiraGlowvedaskincare2024-01-012024-02-15
5@skincarebyiraDermiqueskincare2024-02-152024-03-31
6@skincarebyiraGlowvedaskincare2024-02-012024-02-28
7@fitwithkavyaKicksmithfootwear2024-06-012024-06-30
8@fitwithkavyaStridewellfootwear2024-07-012024-07-31

Output

creator_handlecontract_acontract_boverlap_days
@skincarebyira451
@skincarebyira5614
@techbyrohan1211
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.

Creators Booked by Two Rival Brands at Once — SQL Problem

Creator contracts carry category exclusivity: a creator must not promote two different brands in the same category during overlapping periods.

  • Difficulty: Hard
  • Topics: Joins, Dates, Conditional Logic
  • Dialect: MySQL
  • Problem: #297

Problem statement

Creator contracts carry category exclusivity: a creator must not promote two different brands in the same category during overlapping periods. Periods run from start_date to end_date inclusive, so a contract ending on the day another starts overlaps it by one day. Two contracts with the same brand never conflict.

Return every conflicting pair once, with creator_handle, contract_a (the smaller contract_id), contract_b (the larger) and overlap_days (the number of days both run). Order by creator_handle, then contract_a, then contract_b.

Tables

Table: CreatorContract

ColumnType
contract_idint
creator_handlevarchar
brandvarchar
categoryenum(audio, skincare, footwear)
start_datedate
end_datedate

Primary key: contract_id.

One row per signed contract; end_date is never before start_date. Handles and brands are stored in one consistent case.

Examples

Example 1

CreatorContract

contract_idcreator_handlebrandcategorystart_dateend_date
1@techbyrohanSonicbayaudio2024-03-012024-04-30
2@techbyrohanWavelyaudio2024-04-202024-05-31
3@techbyrohanGlowvedaskincare2024-04-012024-04-30
4@skincarebyiraGlowvedaskincare2024-01-012024-02-15
5@skincarebyiraDermiqueskincare2024-02-152024-03-31
6@skincarebyiraGlowvedaskincare2024-02-012024-02-28
7@fitwithkavyaKicksmithfootwear2024-06-012024-06-30
8@fitwithkavyaStridewellfootwear2024-07-012024-07-31

Output

creator_handlecontract_acontract_boverlap_days
@skincarebyira451
@skincarebyira5614
@techbyrohan1211

How to solve Creators Booked by Two Rival Brands at Once

A conflict is a relation between two rows of the same table, so self-join CreatorContract on the same creator and category, different brands, and a.contract_id < b.contract_id — that ordering lists each unordered pair exactly once and excludes pairing a contract with itself.

Two closed intervals [a.start, a.end] and [b.start, b.end] overlap if and only if a.start <= b.end AND b.start <= a.end. Both comparisons include equality because the dates are inclusive: Glowveda ending on 15 February and Dermique starting that day share one day. Contracts with the same brand (contract 4 and 6) are a renewal, not a conflict, and the brand <> condition drops them.

The shared stretch starts at the later start and ends at the earlier end, so its length is DATEDIFF(LEAST(ends), GREATEST(starts)) + 1. The alternative computes that length for every candidate pair with CASE instead of LEAST/GREATEST and keeps the positive ones, which is the same overlap test in arithmetic form. The pair search is quadratic within a creator and category; an index on (creator_handle, category) keeps it small.

Reference solution (MySQL)

SELECT a.creator_handle, a.contract_id AS contract_a, b.contract_id AS contract_b,
       DATEDIFF(LEAST(a.end_date, b.end_date), GREATEST(a.start_date, b.start_date)) + 1 AS overlap_days
FROM CreatorContract a
JOIN CreatorContract b
  ON b.creator_handle = a.creator_handle
 AND b.category = a.category
 AND b.brand <> a.brand
 AND a.contract_id < b.contract_id
 AND a.start_date <= b.end_date
 AND b.start_date <= a.end_date
ORDER BY a.creator_handle, contract_a, contract_b

Another way

SELECT creator_handle, contract_a, contract_b, overlap_days FROM (
  SELECT a.creator_handle, a.contract_id AS contract_a, b.contract_id AS contract_b,
         DATEDIFF(CASE WHEN a.end_date < b.end_date THEN a.end_date ELSE b.end_date END,
                  CASE WHEN a.start_date > b.start_date THEN a.start_date ELSE b.start_date END) + 1 AS overlap_days
  FROM CreatorContract a, CreatorContract b
  WHERE a.creator_handle = b.creator_handle AND a.category = b.category AND a.brand <> b.brand AND a.contract_id < b.contract_id
) p WHERE overlap_days > 0
ORDER BY creator_handle, contract_a, contract_b

← Dark Days Inside Campaign Flight Dates · Airport Drop Trips Charged With Surge Pricing →