Dream11: Selection Percentage of Every Player per Match
The "Selected by" figure on a player card tells Dream11 users how many fantasy teams for that match include the player.
- Difficulty: Medium
- Topics: Joins, Aggregation, Subqueries
- Dialect: MySQL
- Problem: #631
Problem statement
The "Selected by" figure on a player card tells Dream11 users how many fantasy teams for that match include the player. The product analytics team wants it computed from the saved teams.
For every match and every player picked in at least one team for it, return match_id, player_name and selected_by_pct: the percentage of that match's teams that include the player, rounded to 2 decimals. A team with no picks in this extract still counts in its match's total. Order by match_id, then selected_by_pct descending, then player_name.
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 fantasy team saved for a match.
Table: TeamPick
| Column | Type |
|---|---|
| team_id | int |
| player_id | int |
Primary key: team_id, player_id.
The extract keeps only the picks among a few headline players, so a team lists fewer than 11 rows here.
Table: Player
| Column | Type |
|---|---|
| player_id | int |
| player_name | varchar |
Primary key: player_id.
Player names are unique.
Examples
Example 1
UserTeam
| team_id | user_id | match_id |
|---|---|---|
| 1 | 71 | 501 |
| 2 | 72 | 501 |
| 3 | 73 | 501 |
| 4 | 74 | 501 |
| 5 | 71 | 502 |
| 6 | 75 | 502 |
| 7 | 76 | 502 |
TeamPick
| team_id | player_id |
|---|---|
| 1 | 1 |
| 1 | 2 |
| 2 | 1 |
| 2 | 3 |
| 3 | 1 |
| 3 | 2 |
| 4 | 3 |
| 5 | 4 |
| 5 | 5 |
| 6 | 4 |
Player
| player_id | player_name |
|---|---|
| 1 | Arjun Rao |
| 2 | Kabir Khan |
| 3 | Rohan Iyer |
| 4 | Dev Nair |
| 5 | Ishaan Das |
| 6 | Karan Mehta |
Output
| match_id | player_name | selected_by_pct |
|---|---|---|
| 501 | Arjun Rao | 75 |
| 501 | Kabir Khan | 50 |
| 501 | Rohan Iyer | 50 |
| 502 | Dev Nair | 66.67 |
| 502 | Ishaan Das | 33.33 |
How to solve Dream11: Selection Percentage of Every Player per Match
A selection percentage is a ratio of two counts at different grains: the numerator is per (match, player), the denominator is per match. Mixing them in one GROUP BY does not work, so the query computes the denominator on its own — match_teams, the number of saved teams per match — and joins it back.
The numerator is the number of pick rows for a player in a match. Since (team_id, player_id) is the primary key of TeamPick, a player appears at most once per team, so COUNT(*) after the join is the number of teams that picked them. Taking the denominator from UserTeam is the important detail: counting distinct teams among the picks would ignore the teams that left the player (and everyone else in the extract) out, inflating every percentage.
100 * COUNT(*) / teams is decimal division, rounded to two places. The first alternative uses a correlated scalar subquery for the denominator; the second counts picks with a window function and removes the duplicate rows with DISTINCT. All read the picks once; the subquery versions rely on an index on UserTeam.match_id.
Reference solution (MySQL)
WITH match_teams AS (
SELECT match_id, COUNT(*) AS teams FROM UserTeam GROUP BY match_id
)
SELECT ut.match_id, p.player_name, ROUND(100 * COUNT(*) / mt.teams, 2) AS selected_by_pct
FROM TeamPick tp
JOIN UserTeam ut ON ut.team_id = tp.team_id
JOIN Player p ON p.player_id = tp.player_id
JOIN match_teams mt ON mt.match_id = ut.match_id
GROUP BY ut.match_id, p.player_id, p.player_name, mt.teams
ORDER BY ut.match_id, selected_by_pct DESC, p.player_nameAnother way
SELECT ut.match_id, p.player_name,
ROUND(100 * COUNT(*) / (SELECT COUNT(*) FROM UserTeam x WHERE x.match_id = ut.match_id), 2) AS selected_by_pct
FROM TeamPick tp JOIN UserTeam ut ON ut.team_id = tp.team_id JOIN Player p ON p.player_id = tp.player_id
GROUP BY ut.match_id, p.player_id, p.player_name
ORDER BY ut.match_id, selected_by_pct DESC, p.player_nameAnother way
SELECT DISTINCT match_id, player_name, ROUND(100 * picks / teams, 2) AS selected_by_pct
FROM (SELECT ut.match_id, p.player_name,
COUNT(*) OVER (PARTITION BY ut.match_id, tp.player_id) AS picks,
(SELECT COUNT(*) FROM UserTeam y WHERE y.match_id = ut.match_id) AS teams
FROM TeamPick tp JOIN UserTeam ut ON ut.team_id = tp.team_id JOIN Player p ON p.player_id = tp.player_id) z
ORDER BY match_id, selected_by_pct DESC, player_name← Dream11: Deposits Above a User's Own Monthly Limit · Dream11: Most-Picked Captain in Every Match →