Dream11: Contest Winners From Player Points With C and VC Boosts
After a match, Dream11 computes every fantasy team's score from its players' points and declares each contest's winner.
- Difficulty: Hard
- Topics: Joins, Aggregation, Window Functions, Conditional Logic
- Dialect: MySQL
- Problem: #644
Problem statement
After a match, Dream11 computes every fantasy team's score from its players' points and declares each contest's winner. A team's score is the sum of its players' points, where the captain's points count 2× and the vice-captain's 1.5×. A picked player with no row in PlayerPoints did not play and scores 0. All contests here are on the same match.
For every contest, return the entry (or entries, if tied) with the highest team score: contest_id, team_id, user_id and team_points. Order by contest_id, then 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: PlayerPoints
| Column | Type |
|---|---|
| player_id | int |
| points | decimal |
Primary key: player_id.
Base fantasy points of each player who played (steps of 0.5, may be negative).
Table: UserTeam
| Column | Type |
|---|---|
| team_id | int |
| user_id | int |
| captain_id | int |
| vice_captain_id | int |
Primary key: team_id.
The captain and vice-captain are two different players of the team's picks.
Table: TeamPick
| Column | Type |
|---|---|
| team_id | int |
| player_id | int |
Primary key: team_id, player_id.
The players in each team (shortened in this extract).
Table: ContestEntry
| Column | Type |
|---|---|
| entry_id | int |
| contest_id | int |
| team_id | int |
Primary key: entry_id.
A team can be entered into several contests, at most once each.
Examples
Example 1
PlayerPoints
| player_id | points |
|---|---|
| 1 | 80 |
| 2 | 42.5 |
| 3 | 10 |
| 4 | -2 |
| 5 | 61 |
UserTeam
| team_id | user_id | captain_id | vice_captain_id |
|---|---|---|---|
| 1 | 71 | 1 | 2 |
| 2 | 72 | 2 | 1 |
| 3 | 73 | 5 | 1 |
| 4 | 74 | 6 | 5 |
TeamPick
| team_id | player_id |
|---|---|
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 1 |
| 2 | 2 |
| 2 | 4 |
| 3 | 1 |
| 3 | 5 |
| 3 | 3 |
| 4 | 6 |
| 4 | 5 |
| 4 | 1 |
ContestEntry
| entry_id | contest_id | team_id |
|---|---|---|
| 100 | 900 | 1 |
| 101 | 900 | 2 |
| 102 | 900 | 3 |
| 103 | 901 | 2 |
| 104 | 901 | 4 |
Output
| contest_id | team_id | user_id | team_points |
|---|---|---|---|
| 900 | 3 | 73 | 252 |
| 901 | 2 | 72 | 203 |
How to solve Dream11: Contest Winners From Player Points With C and VC Boosts
The score is a property of the team, not of the contest, so it is computed once per team in team_score and then attached to each entry. For every pick the multiplier comes from comparing the pick with the team's captain and vice-captain — CASE WHEN tp.player_id = ut.captain_id THEN 2 WHEN … = vice_captain_id THEN 1.5 ELSE 1 END — and the weighted points are summed.
Players who did not play have no PlayerPoints row. An inner join would delete those picks, which happens to give the same total, but it would also delete a whole team if none of its players played; the LEFT JOIN with COALESCE(pp.points, 0) states the rule directly. Negative points are possible and the multiplier applies to them too: a captain on −2 costs 4.
The winners are the entries whose score equals the contest's maximum, found with RANK() … = 1 so ties are all kept. The alternative computes the boost differently — the plain sum of all picks plus one extra copy of the captain's points and half a copy of the vice-captain's — and finds the best per contest with a MAX join instead of a window. Steps of 0.5 times 1.5 are multiples of 0.25, exact in binary, so equal scores compare as equal.
Reference solution (MySQL)
WITH team_score AS (
SELECT ut.team_id, ut.user_id,
SUM(COALESCE(pp.points, 0) * CASE WHEN tp.player_id = ut.captain_id THEN 2
WHEN tp.player_id = ut.vice_captain_id THEN 1.5
ELSE 1 END) AS team_points
FROM UserTeam ut
JOIN TeamPick tp ON tp.team_id = ut.team_id
LEFT JOIN PlayerPoints pp ON pp.player_id = tp.player_id
GROUP BY ut.team_id, ut.user_id
),
ranked AS (
SELECT e.contest_id, s.team_id, s.user_id, s.team_points,
RANK() OVER (PARTITION BY e.contest_id ORDER BY s.team_points DESC) AS pos
FROM ContestEntry e
JOIN team_score s ON s.team_id = e.team_id
)
SELECT contest_id, team_id, user_id, team_points
FROM ranked
WHERE pos = 1
ORDER BY contest_id, team_idAnother way
WITH base AS (
SELECT tp.team_id, SUM(COALESCE(pp.points, 0)) AS raw_points
FROM TeamPick tp LEFT JOIN PlayerPoints pp ON pp.player_id = tp.player_id GROUP BY tp.team_id
),
team_score AS (
SELECT ut.team_id, ut.user_id,
b.raw_points + COALESCE(c.points, 0) + 0.5 * COALESCE(v.points, 0) AS team_points
FROM UserTeam ut JOIN base b ON b.team_id = ut.team_id
LEFT JOIN PlayerPoints c ON c.player_id = ut.captain_id
LEFT JOIN PlayerPoints v ON v.player_id = ut.vice_captain_id
),
per_contest AS (
SELECT e.contest_id, s.team_id, s.user_id, s.team_points FROM ContestEntry e JOIN team_score s ON s.team_id = e.team_id
)
SELECT a.contest_id, a.team_id, a.user_id, a.team_points
FROM per_contest a
JOIN (SELECT contest_id, MAX(team_points) AS best FROM per_contest GROUP BY contest_id) m
ON m.contest_id = a.contest_id AND m.best = a.team_points
ORDER BY a.contest_id, a.team_id← Dream11: Top Three Users by Net Winnings in Each Sport · Dream11: Withdrawals Right After a Deposit With No Play Between →