Fantasy Gameweek Scores With the Captain's Double Points

Medium
460.

Fantasy Gameweek Scores With the Captain's Double Points

MediumJoinsAggregation

MySQL

In the fantasy game a team's score for a gameweek is the sum of its picked players' points that gameweek, with the captain's points doubled. A picked player who did not play has no row in PlayerScore and scores 0.

Return one row for every team and gameweek in which the team made picks, with the columns team_name, gameweek and total_points. Order the rows by gameweek, then total_points descending, then team_name.

Tables

Table: FantasySquad

ColumnType
team_idint
team_namevarchar

Primary key: team_id.

One row per fantasy team; team names are distinct.

Table: GameweekPick

ColumnType
team_idint
gameweekint
player_idint
is_captainbool

Primary key (together): team_id, gameweek, player_id.

One row per player a team picked for a gameweek; at most one pick per team and gameweek has is_captain = 1.

Table: PlayerScore

ColumnType
player_idint
gameweekint
pointsint

Primary key (together): player_id, gameweek.

A player's fantasy points in a gameweek they played; points can be negative.

Example

The rows must come back in the order shown.

FantasySquad

team_idteam_name
1Spin Doctors XI
2Powerplay Pirates
3Yorker Kings

GameweekPick

team_idgameweekplayer_idis_captain
11101
11110
11120
21111
21130
12100
12131
32120

PlayerScore

player_idgameweekpoints
10142
11118
121-2
13160
10225
1328

Output

team_namegameweektotal_points
Spin Doctors XI1100
Powerplay Pirates196
Spin Doctors XI241
Yorker Kings20
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.

Fantasy Gameweek Scores With the Captain's Double Points

In the fantasy game a team's score for a gameweek is the sum of its picked players' points that gameweek, with the captain's points doubled.

  • Difficulty: Medium
  • Topics: Joins, Aggregation
  • Dialect: MySQL
  • Problem: #460

Problem statement

In the fantasy game a team's score for a gameweek is the sum of its picked players' points that gameweek, with the captain's points doubled. A picked player who did not play has no row in PlayerScore and scores 0.

Return one row for every team and gameweek in which the team made picks, with the columns team_name, gameweek and total_points. Order the rows by gameweek, then total_points descending, then team_name.

Tables

Table: FantasySquad

ColumnType
team_idint
team_namevarchar

Primary key: team_id.

One row per fantasy team; team names are distinct.

Table: GameweekPick

ColumnType
team_idint
gameweekint
player_idint
is_captainbool

Primary key: team_id, gameweek, player_id.

One row per player a team picked for a gameweek; at most one pick per team and gameweek has is_captain = 1.

Table: PlayerScore

ColumnType
player_idint
gameweekint
pointsint

Primary key: player_id, gameweek.

A player's fantasy points in a gameweek they played; points can be negative.

Examples

Example 1

FantasySquad

team_idteam_name
1Spin Doctors XI
2Powerplay Pirates
3Yorker Kings

GameweekPick

team_idgameweekplayer_idis_captain
11101
11110
11120
21111
21130
12100
12131
32120

PlayerScore

player_idgameweekpoints
10142
11118
121-2
13160
10225
1328

Output

team_namegameweektotal_points
Spin Doctors XI1100
Powerplay Pirates196
Spin Doctors XI241
Yorker Kings20

How to solve Fantasy Gameweek Scores With the Captain's Double Points

The grain of the answer is (team, gameweek), and those come from GameweekPick. Each pick needs that player's points for the same gameweek, so the join to PlayerScore is on both player_id and gameweek — joining on the player alone would add up every gameweek's points.

Some picked players did not play and have no score row. An inner join would silently drop those picks; a LEFT JOIN keeps them with NULL points, which COALESCE(s.points, 0) turns into 0. The captain's double is a multiplier per row, CASE WHEN is_captain = 1 THEN 2 ELSE 1 END (or 1 + is_captain, since the flag is 0 or 1).

Grouping by team and gameweek and summing gives the score; a team that skipped a gameweek has no picks and no row. The alternative looks the score up with a correlated scalar subquery, which is the same left-join semantics in another shape. Order by gameweek, best score first, then name. With indexes on the primary keys the work is one lookup per pick.

Reference solution (MySQL)

SELECT t.team_name, p.gameweek,
       SUM(CASE WHEN p.is_captain = 1 THEN 2 ELSE 1 END * COALESCE(s.points, 0)) AS total_points
FROM GameweekPick p
JOIN FantasySquad t ON t.team_id = p.team_id
LEFT JOIN PlayerScore s ON s.player_id = p.player_id AND s.gameweek = p.gameweek
GROUP BY t.team_id, t.team_name, p.gameweek
ORDER BY p.gameweek, total_points DESC, t.team_name

Another way

SELECT t.team_name, p.gameweek, SUM((1 + p.is_captain) * IFNULL((SELECT s.points FROM PlayerScore s WHERE s.player_id = p.player_id AND s.gameweek = p.gameweek), 0)) AS total_points FROM GameweekPick p JOIN FantasySquad t ON t.team_id = p.team_id GROUP BY t.team_name, p.gameweek ORDER BY p.gameweek, total_points DESC, t.team_name

← Death-Overs Economy From Ball-by-Ball Data · Auction Buys Above Their Franchise's Average Price →