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
| Column | Type |
|---|---|
| team_id | int |
| team_name | varchar |
Primary key: team_id.
One row per fantasy team; team names are distinct.
Table: GameweekPick
| Column | Type |
|---|---|
| team_id | int |
| gameweek | int |
| player_id | int |
| is_captain | bool |
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
| Column | Type |
|---|---|
| player_id | int |
| gameweek | int |
| points | int |
Primary key: player_id, gameweek.
A player's fantasy points in a gameweek they played; points can be negative.
Examples
Example 1
FantasySquad
| team_id | team_name |
|---|---|
| 1 | Spin Doctors XI |
| 2 | Powerplay Pirates |
| 3 | Yorker Kings |
GameweekPick
| team_id | gameweek | player_id | is_captain |
|---|---|---|---|
| 1 | 1 | 10 | 1 |
| 1 | 1 | 11 | 0 |
| 1 | 1 | 12 | 0 |
| 2 | 1 | 11 | 1 |
| 2 | 1 | 13 | 0 |
| 1 | 2 | 10 | 0 |
| 1 | 2 | 13 | 1 |
| 3 | 2 | 12 | 0 |
PlayerScore
| player_id | gameweek | points |
|---|---|---|
| 10 | 1 | 42 |
| 11 | 1 | 18 |
| 12 | 1 | -2 |
| 13 | 1 | 60 |
| 10 | 2 | 25 |
| 13 | 2 | 8 |
Output
| team_name | gameweek | total_points |
|---|---|---|
| Spin Doctors XI | 1 | 100 |
| Powerplay Pirates | 1 | 96 |
| Spin Doctors XI | 2 | 41 |
| Yorker Kings | 2 | 0 |
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_nameAnother 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 →