Dream11: Saved Teams Carrying Players Left Out of the XI

Medium
633.

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. 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

ColumnType
team_idint
user_idint
match_idint

Primary key: team_id.

One row per saved fantasy team.

Table: TeamPick

ColumnType
team_idint
player_idint

Primary key (together): team_id, player_id.

The players in a team (the extract lists only some of them).

Table: PlayingXI

ColumnType
match_idint
player_idint

Primary key (together): match_id, player_id.

The announced line-up of a match; no rows for a match whose toss has not happened yet.

Example

The rows must come back in the order shown.

UserTeam

team_iduser_idmatch_id
171501
272501
373501
474502
575501

TeamPick

team_idplayer_id
11
12
13
21
24
25
32
33
47
48
2 more rows

PlayingXI

match_idplayer_id
5011
5012
5013
5016

Output

team_iduser_idbenched_picks
2722
5751
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: 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

ColumnType
team_idint
user_idint
match_idint

Primary key: team_id.

One row per saved fantasy team.

Table: TeamPick

ColumnType
team_idint
player_idint

Primary key: team_id, player_id.

The players in a team (the extract lists only some of them).

Table: PlayingXI

ColumnType
match_idint
player_idint

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_iduser_idmatch_id
171501
272501
373501
474502
575501

TeamPick

team_idplayer_id
11
12
13
21
24
25
32
33
47
48
55
56

PlayingXI

match_idplayer_id
5011
5012
5013
5016

Output

team_iduser_idbenched_picks
2722
5751

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_id

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

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