Dream11: Selection Percentage of Every Player per Match

Medium
631.

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

ColumnType
team_idint
user_idint
match_idint

Primary key: team_id.

One row per fantasy team saved for a match.

Table: TeamPick

ColumnType
team_idint
player_idint

Primary key (together): 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

ColumnType
player_idint
player_namevarchar

Primary key: player_id.

Player names are unique.

Example

The rows must come back in the order shown.

UserTeam

team_iduser_idmatch_id
171501
272501
373501
474501
571502
675502
776502

TeamPick

team_idplayer_id
11
12
21
23
31
32
43
54
55
64

Player

player_idplayer_name
1Arjun Rao
2Kabir Khan
3Rohan Iyer
4Dev Nair
5Ishaan Das
6Karan Mehta

Output

match_idplayer_nameselected_by_pct
501Arjun Rao75
501Kabir Khan50
501Rohan Iyer50
502Dev Nair66.67
502Ishaan Das33.33
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: 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

ColumnType
team_idint
user_idint
match_idint

Primary key: team_id.

One row per fantasy team saved for a match.

Table: TeamPick

ColumnType
team_idint
player_idint

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

ColumnType
player_idint
player_namevarchar

Primary key: player_id.

Player names are unique.

Examples

Example 1

UserTeam

team_iduser_idmatch_id
171501
272501
373501
474501
571502
675502
776502

TeamPick

team_idplayer_id
11
12
21
23
31
32
43
54
55
64

Player

player_idplayer_name
1Arjun Rao
2Kabir Khan
3Rohan Iyer
4Dev Nair
5Ishaan Das
6Karan Mehta

Output

match_idplayer_nameselected_by_pct
501Arjun Rao75
501Kabir Khan50
501Rohan Iyer50
502Dev Nair66.67
502Ishaan Das33.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_name

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

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