Dream11: Head-to-Head Fixtures Still to Play in Private Leagues

Medium
634.

Dream11: Head-to-Head Fixtures Still to Play in Private Leagues

MediumJoinsSubqueries

MySQL

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

ColumnType
league_idint
user_idint
joined_ondate

Primary key (together): league_id, user_id.

One row per member of a private league; a user can belong to several leagues.

Table: H2HMatch

ColumnType
h2h_idint
league_idint
user_aint
user_bint

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.

Example

The rows must come back in the order shown.

LeagueMember

league_iduser_idjoined_on
1112025-03-01
1122025-03-01
1132025-03-02
1142025-03-05
2112025-03-03
2152025-03-03
2162025-03-04
3172025-03-06

H2HMatch

h2h_idleague_iduser_auser_b
10011112
10111411
10211312
10311211
10421615
10521314

Output

league_iduser_oneuser_two
11113
11214
11314
21115
21116
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.

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

ColumnType
league_idint
user_idint
joined_ondate

Primary key: league_id, user_id.

One row per member of a private league; a user can belong to several leagues.

Table: H2HMatch

ColumnType
h2h_idint
league_idint
user_aint
user_bint

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_iduser_idjoined_on
1112025-03-01
1122025-03-01
1132025-03-02
1142025-03-05
2112025-03-03
2152025-03-03
2162025-03-04
3172025-03-06

H2HMatch

h2h_idleague_iduser_auser_b
10011112
10111411
10211312
10311211
10421615
10521314

Output

league_iduser_oneuser_two
11113
11214
11314
21115
21116

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_two

Another 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_two

Another 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 →