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
| Column | Type |
|---|---|
| contract_id | int |
| creator_handle | varchar |
| brand | varchar |
| category | enum(audio, skincare, footwear) |
| start_date | date |
| end_date | date |
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_id | creator_handle | brand | category | start_date | end_date |
|---|---|---|---|---|---|
| 1 | @techbyrohan | Sonicbay | audio | 2024-03-01 | 2024-04-30 |
| 2 | @techbyrohan | Wavely | audio | 2024-04-20 | 2024-05-31 |
| 3 | @techbyrohan | Glowveda | skincare | 2024-04-01 | 2024-04-30 |
| 4 | @skincarebyira | Glowveda | skincare | 2024-01-01 | 2024-02-15 |
| 5 | @skincarebyira | Dermique | skincare | 2024-02-15 | 2024-03-31 |
| 6 | @skincarebyira | Glowveda | skincare | 2024-02-01 | 2024-02-28 |
| 7 | @fitwithkavya | Kicksmith | footwear | 2024-06-01 | 2024-06-30 |
| 8 | @fitwithkavya | Stridewell | footwear | 2024-07-01 | 2024-07-31 |
Output
| creator_handle | contract_a | contract_b | overlap_days |
|---|---|---|---|
| @skincarebyira | 4 | 5 | 1 |
| @skincarebyira | 5 | 6 | 14 |
| @techbyrohan | 1 | 2 | 11 |
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_bAnother 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 →