Dream11: Contest Winners From Player Points With C and VC Boosts

Hard
644.

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

ColumnType
player_idint
pointsdecimal

Primary key: player_id.

Base fantasy points of each player who played (steps of 0.5, may be negative).

Table: UserTeam

ColumnType
team_idint
user_idint
captain_idint
vice_captain_idint

Primary key: team_id.

The captain and vice-captain are two different players of the team's picks.

Table: TeamPick

ColumnType
team_idint
player_idint

Primary key (together): team_id, player_id.

The players in each team (shortened in this extract).

Table: ContestEntry

ColumnType
entry_idint
contest_idint
team_idint

Primary key: entry_id.

A team can be entered into several contests, at most once each.

Example

The rows must come back in the order shown.

PlayerPoints

player_idpoints
180
242.5
310
4-2
561

UserTeam

team_iduser_idcaptain_idvice_captain_id
17112
27221
37351
47465

TeamPick

team_idplayer_id
11
12
13
21
22
24
31
35
33
46
2 more rows

ContestEntry

entry_idcontest_idteam_id
1009001
1019002
1029003
1039012
1049014

Output

contest_idteam_iduser_idteam_points
900373252
901272203
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: 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

ColumnType
player_idint
pointsdecimal

Primary key: player_id.

Base fantasy points of each player who played (steps of 0.5, may be negative).

Table: UserTeam

ColumnType
team_idint
user_idint
captain_idint
vice_captain_idint

Primary key: team_id.

The captain and vice-captain are two different players of the team's picks.

Table: TeamPick

ColumnType
team_idint
player_idint

Primary key: team_id, player_id.

The players in each team (shortened in this extract).

Table: ContestEntry

ColumnType
entry_idint
contest_idint
team_idint

Primary key: entry_id.

A team can be entered into several contests, at most once each.

Examples

Example 1

PlayerPoints

player_idpoints
180
242.5
310
4-2
561

UserTeam

team_iduser_idcaptain_idvice_captain_id
17112
27221
37351
47465

TeamPick

team_idplayer_id
11
12
13
21
22
24
31
35
33
46
45
41

ContestEntry

entry_idcontest_idteam_id
1009001
1019002
1029003
1039012
1049014

Output

contest_idteam_iduser_idteam_points
900373252
901272203

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_id

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