Dream11: Most-Picked Captain in Every Match

Medium
632.

Dream11: Most-Picked Captain in Every Match

Before each match, Dream11's content team publishes the most popular captain choice. A captain earns double points, so the pick says a lot about what users expect.

For every match with at least one saved team, return the player (or players, if tied) chosen as captain by the most teams. Return match_id, player_name and captain_picks, in any order.

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
captain_idint
vice_captain_idint

Primary key: team_id.

One row per saved team; captain_id and vice_captain_id are different players of the team.

Table: Player

ColumnType
player_idint
player_namevarchar

Primary key: player_id.

One row per player.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

UserTeam

team_iduser_idmatch_idcaptain_idvice_captain_id
17150112
27250113
37350121
47450112
57150245
67550254
77650264

Player

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

Output

match_idplayer_namecaptain_picks
501Arjun Rao3
502Karan Mehta1
502Ishaan Das1
502Dev Nair1
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: Most-Picked Captain in Every Match — SQL Problem

Before each match, Dream11's content team publishes the most popular captain choice.

  • Difficulty: Medium
  • Topics: Window Functions, Joins, Aggregation
  • Dialect: MySQL
  • Problem: #632

Problem statement

Before each match, Dream11's content team publishes the most popular captain choice. A captain earns double points, so the pick says a lot about what users expect.

For every match with at least one saved team, return the player (or players, if tied) chosen as captain by the most teams. Return match_id, player_name and captain_picks, in any order.

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
captain_idint
vice_captain_idint

Primary key: team_id.

One row per saved team; captain_id and vice_captain_id are different players of the team.

Table: Player

ColumnType
player_idint
player_namevarchar

Primary key: player_id.

One row per player.

Examples

Example 1

UserTeam

team_iduser_idmatch_idcaptain_idvice_captain_id
17150112
27250113
37350121
47450112
57150245
67550254
77650264

Player

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

Output

match_idplayer_namecaptain_picks
501Arjun Rao3
502Karan Mehta1
502Ishaan Das1
502Dev Nair1

How to solve Dream11: Most-Picked Captain in Every Match

This is a "top per group, keeping ties" question in two steps. First collapse the teams to one row per (match, captain) with COUNT(*) — that is how many teams made each player captain. Then, inside each match, keep the rows whose count is the largest.

RANK() OVER (PARTITION BY match_id ORDER BY COUNT(*) DESC) does the second step in the same query: window functions run after GROUP BY, so they can order by the aggregate. Every captain sharing the top count gets rank 1, which is why RANK (or DENSE_RANK) is right and ROW_NUMBER is wrong — it would arbitrarily pick one of the tied players. The final join turns the captain's id into a name.

Without windows the same result comes from comparing each count with the match's maximum, either through HAVING COUNT(*) = (SELECT MAX(…)) or with NOT EXISTS a strictly larger count in the same match. Matches without any teams never produce a group and are absent. One grouping pass over UserTeam.

Reference solution (MySQL)

WITH picks AS (
  SELECT match_id, captain_id, COUNT(*) AS captain_picks,
         RANK() OVER (PARTITION BY match_id ORDER BY COUNT(*) DESC) AS pos
  FROM UserTeam
  GROUP BY match_id, captain_id
)
SELECT k.match_id, p.player_name, k.captain_picks
FROM picks k
JOIN Player p ON p.player_id = k.captain_id
WHERE k.pos = 1

Another way

SELECT t.match_id, p.player_name, COUNT(*) AS captain_picks
FROM UserTeam t JOIN Player p ON p.player_id = t.captain_id
GROUP BY t.match_id, t.captain_id, p.player_name
HAVING COUNT(*) = (SELECT MAX(c) FROM (SELECT match_id, COUNT(*) AS c FROM UserTeam GROUP BY match_id, captain_id) x WHERE x.match_id = t.match_id)

Another way

WITH c AS (SELECT match_id, captain_id, COUNT(*) AS n FROM UserTeam GROUP BY match_id, captain_id)
SELECT c.match_id, p.player_name, c.n AS captain_picks
FROM c JOIN Player p ON p.player_id = c.captain_id
WHERE NOT EXISTS (SELECT 1 FROM c d WHERE d.match_id = c.match_id AND d.n > c.n)

← Dream11: Selection Percentage of Every Player per Match · Dream11: Saved Teams Carrying Players Left Out of the XI →