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
| Column | Type |
|---|---|
| team_id | int |
| user_id | int |
| match_id | int |
| captain_id | int |
| vice_captain_id | int |
Primary key: team_id.
One row per saved team; captain_id and vice_captain_id are different players of the team.
Table: Player
| Column | Type |
|---|---|
| player_id | int |
| player_name | varchar |
Primary key: player_id.
One row per player.
Examples
Example 1
UserTeam
| team_id | user_id | match_id | captain_id | vice_captain_id |
|---|---|---|---|---|
| 1 | 71 | 501 | 1 | 2 |
| 2 | 72 | 501 | 1 | 3 |
| 3 | 73 | 501 | 2 | 1 |
| 4 | 74 | 501 | 1 | 2 |
| 5 | 71 | 502 | 4 | 5 |
| 6 | 75 | 502 | 5 | 4 |
| 7 | 76 | 502 | 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 | captain_picks |
|---|---|---|
| 501 | Arjun Rao | 3 |
| 502 | Karan Mehta | 1 |
| 502 | Ishaan Das | 1 |
| 502 | Dev Nair | 1 |
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 = 1Another 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 →