Dream11: Head-to-Head Fixtures Still to Play in Private Leagues
Some groups of friends run a season-long private league on Dream11 in round-robin style: every two members of a league meet in exactly one head-to-head.
- Difficulty: Medium
- Topics: Joins, Subqueries
- Dialect: MySQL
- Problem: #634
Problem statement
Some groups of friends run a season-long private league on Dream11 in round-robin style: every two members of a league meet in exactly one head-to-head. The private-contests team needs the fixtures that still have to be created so the remaining head-to-heads can be scheduled.
For every league, list each pair of its members that has not yet played a head-to-head in that league. A played head-to-head may be stored with either member as user_a; a pair from a different league does not count. Return league_id, user_one and user_two, with user_one the smaller user id of the pair, so each pair appears once. Order by league_id, then user_one, then user_two.
A practice scenario set at Dream11: the tables and data are invented, and this is not a question from, or endorsed by, Dream11.
Tables
Table: LeagueMember
| Column | Type |
|---|---|
| league_id | int |
| user_id | int |
| joined_on | date |
Primary key: league_id, user_id.
One row per member of a private league; a user can belong to several leagues.
Table: H2HMatch
| Column | Type |
|---|---|
| h2h_id | int |
| league_id | int |
| user_a | int |
| user_b | int |
Primary key: h2h_id.
A head-to-head already played between two members of the league, in no particular order of user_a and user_b. A rematch after an abandoned match can appear twice.
Examples
Example 1
LeagueMember
| league_id | user_id | joined_on |
|---|---|---|
| 1 | 11 | 2025-03-01 |
| 1 | 12 | 2025-03-01 |
| 1 | 13 | 2025-03-02 |
| 1 | 14 | 2025-03-05 |
| 2 | 11 | 2025-03-03 |
| 2 | 15 | 2025-03-03 |
| 2 | 16 | 2025-03-04 |
| 3 | 17 | 2025-03-06 |
H2HMatch
| h2h_id | league_id | user_a | user_b |
|---|---|---|---|
| 100 | 1 | 11 | 12 |
| 101 | 1 | 14 | 11 |
| 102 | 1 | 13 | 12 |
| 103 | 1 | 12 | 11 |
| 104 | 2 | 16 | 15 |
| 105 | 2 | 13 | 14 |
Output
| league_id | user_one | user_two |
|---|---|---|
| 1 | 11 | 13 |
| 1 | 12 | 14 |
| 1 | 13 | 14 |
| 2 | 11 | 15 |
| 2 | 11 | 16 |
How to solve Dream11: Head-to-Head Fixtures Still to Play in Private Leagues
A round-robin schedule is every unordered pair of members, and the way to list unordered pairs in SQL is a self join with an inequality: LeagueMember m1 JOIN LeagueMember m2 ON m2.league_id = m1.league_id AND m1.user_id < m2.user_id. The equality keeps both members in the same league; the strict < does two jobs at once — it drops the pairing of a member with themselves, and it keeps only one of the two orders (11 vs 12, never also 12 vs 11). A league of n members yields n(n − 1)/2 pairs, and a league of one member yields none.
What remains is an anti join against the matches already played. Because a played match may name either member first, it is normalised with LEAST(user_a, user_b) and GREATEST(user_a, user_b), which is exactly the shape of the generated pair; a LEFT JOIN on that plus the league id, keeping the rows where nothing matched, leaves the fixtures still owed. A rematch stored twice simply matches twice and is still excluded, and a meeting in a different league does not match because the league is part of the condition.
The alternatives test both orders explicitly in a NOT EXISTS, or remove the played pairs (in both orientations, via UNION) with a row-value NOT IN. Pair generation is quadratic in a league's size, which is tiny for a friends' league.
Reference solution (MySQL)
SELECT m1.league_id, m1.user_id AS user_one, m2.user_id AS user_two
FROM LeagueMember m1
JOIN LeagueMember m2 ON m2.league_id = m1.league_id AND m1.user_id < m2.user_id
LEFT JOIN H2HMatch h ON h.league_id = m1.league_id
AND LEAST(h.user_a, h.user_b) = m1.user_id
AND GREATEST(h.user_a, h.user_b) = m2.user_id
WHERE h.h2h_id IS NULL
ORDER BY m1.league_id, user_one, user_twoAnother way
SELECT m1.league_id, m1.user_id AS user_one, m2.user_id AS user_two
FROM LeagueMember m1, LeagueMember m2
WHERE m2.league_id = m1.league_id AND m2.user_id > m1.user_id
AND NOT EXISTS (SELECT 1 FROM H2HMatch h WHERE h.league_id = m1.league_id
AND ((h.user_a = m1.user_id AND h.user_b = m2.user_id) OR (h.user_a = m2.user_id AND h.user_b = m1.user_id)))
ORDER BY m1.league_id, user_one, user_twoAnother way
SELECT a.league_id, a.user_id AS user_one, b.user_id AS user_two
FROM LeagueMember a JOIN LeagueMember b ON b.league_id = a.league_id AND b.user_id > a.user_id
WHERE (a.league_id, a.user_id, b.user_id) NOT IN (
SELECT league_id, user_a, user_b FROM H2HMatch
UNION
SELECT league_id, user_b, user_a FROM H2HMatch)
ORDER BY a.league_id, user_one, user_two← Dream11: Saved Teams Carrying Players Left Out of the XI · Dream11: The Moment Each Contest Filled Up →