Dream11: Saved Teams Carrying Players Left Out of the XI
When the toss happens and the playing XIs are announced, Dream11 reminds users whose saved team includes players who will not play.
- Difficulty: Medium
- Topics: Joins, Subqueries, Aggregation
- Dialect: MySQL
- Problem: #633
Problem statement
When the toss happens and the playing XIs are announced, Dream11 reminds users whose saved team includes players who will not play. The notifications team needs the list.
Only matches whose XI has been announced (at least one row in PlayingXI) are checked; teams for other matches are ignored. For each team in a checked match that picked at least one player missing from that match's announced XI, return team_id, user_id and benched_picks (how many such players). Order by benched_picks descending, then by team_id.
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: UserTeam
| Column | Type |
|---|---|
| team_id | int |
| user_id | int |
| match_id | int |
Primary key: team_id.
One row per saved fantasy team.
Table: TeamPick
| Column | Type |
|---|---|
| team_id | int |
| player_id | int |
Primary key: team_id, player_id.
The players in a team (the extract lists only some of them).
Table: PlayingXI
| Column | Type |
|---|---|
| match_id | int |
| player_id | int |
Primary key: match_id, player_id.
The announced line-up of a match; no rows for a match whose toss has not happened yet.
Examples
Example 1
UserTeam
| team_id | user_id | match_id |
|---|---|---|
| 1 | 71 | 501 |
| 2 | 72 | 501 |
| 3 | 73 | 501 |
| 4 | 74 | 502 |
| 5 | 75 | 501 |
TeamPick
| team_id | player_id |
|---|---|
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 1 |
| 2 | 4 |
| 2 | 5 |
| 3 | 2 |
| 3 | 3 |
| 4 | 7 |
| 4 | 8 |
| 5 | 5 |
| 5 | 6 |
PlayingXI
| match_id | player_id |
|---|---|
| 501 | 1 |
| 501 | 2 |
| 501 | 3 |
| 501 | 6 |
Output
| team_id | user_id | benched_picks |
|---|---|---|
| 2 | 72 | 2 |
| 5 | 75 | 1 |
How to solve Dream11: Saved Teams Carrying Players Left Out of the XI
Each pick needs to be checked against the XI of its own team's match, so the anti join is on two columns: x.match_id = ut.match_id AND x.player_id = tp.player_id. A LEFT JOIN with WHERE x.player_id IS NULL keeps exactly the picks that found no line-up row, and grouping by team counts them. Teams with no benched picks have no surviving rows and disappear, which is what "at least one" asks.
The trap is the match whose toss has not happened. Its PlayingXI is empty, so every one of its picks would look benched and every team would be notified. The extra condition ut.match_id IN (SELECT match_id FROM PlayingXI) restricts the check to announced matches.
The first alternative states both tests with EXISTS / NOT EXISTS. The second joins to the distinct announced matches first and counts the unmatched picks with a conditional SUM, filtering zero counts afterwards. With the primary keys as indexes each pick costs one index probe.
Reference solution (MySQL)
SELECT ut.team_id, ut.user_id, COUNT(*) AS benched_picks
FROM UserTeam ut
JOIN TeamPick tp ON tp.team_id = ut.team_id
LEFT JOIN PlayingXI x ON x.match_id = ut.match_id AND x.player_id = tp.player_id
WHERE x.player_id IS NULL
AND ut.match_id IN (SELECT match_id FROM PlayingXI)
GROUP BY ut.team_id, ut.user_id
ORDER BY benched_picks DESC, ut.team_idAnother way
SELECT ut.team_id, ut.user_id, COUNT(*) AS benched_picks
FROM UserTeam ut JOIN TeamPick tp ON tp.team_id = ut.team_id
WHERE EXISTS (SELECT 1 FROM PlayingXI a WHERE a.match_id = ut.match_id)
AND NOT EXISTS (SELECT 1 FROM PlayingXI x WHERE x.match_id = ut.match_id AND x.player_id = tp.player_id)
GROUP BY ut.team_id, ut.user_id
ORDER BY benched_picks DESC, ut.team_idAnother way
SELECT team_id, user_id, benched_picks FROM (
SELECT ut.team_id, ut.user_id, SUM(CASE WHEN x.player_id IS NULL THEN 1 ELSE 0 END) AS benched_picks
FROM UserTeam ut
JOIN (SELECT DISTINCT match_id FROM PlayingXI) ann ON ann.match_id = ut.match_id
JOIN TeamPick tp ON tp.team_id = ut.team_id
LEFT JOIN PlayingXI x ON x.match_id = ut.match_id AND x.player_id = tp.player_id
GROUP BY ut.team_id, ut.user_id) s
WHERE benched_picks > 0
ORDER BY benched_picks DESC, team_id← Dream11: Most-Picked Captain in Every Match · Dream11: Head-to-Head Fixtures Still to Play in Private Leagues →