Median Fantasy Points per Gameweek by Player Role
The fantasy game's pricing team sets player prices by role and wants a typical gameweek score that big hauls do not distort, so they use the median: the…
- Difficulty: Hard
- Topics: Window Functions, Aggregation, Joins
- Dialect: MySQL
- Problem: #469
Problem statement
The fantasy game's pricing team sets player prices by role and wants a typical gameweek score that big hauls do not distort, so they use the median: the middle score when a role's scores are sorted, or the average of the two middle scores when there is an even number of them.
Over every row of GameweekPoints, return one row per role that has at least one score, with the columns role, scores (how many gameweek scores the role has) and median_points, rounded to 1 decimal place. Order the rows by role.
Tables
Table: FantasyPlayer
| Column | Type |
|---|---|
| player_id | int |
| player_name | varchar |
| role | enum(AR, BAT, BWL, WK) |
Primary key: player_id.
One row per player in the game.
Table: GameweekPoints
| Column | Type |
|---|---|
| player_id | int |
| gameweek | int |
| points | int |
Primary key: player_id, gameweek.
A player's fantasy points in a gameweek they played. player_id is always in FantasyPlayer; points can be negative.
Examples
Example 1
FantasyPlayer
| player_id | player_name | role |
|---|---|---|
| 1 | Rohan Iyer | BAT |
| 2 | Kabir Khan | BAT |
| 3 | Aarav Das | BWL |
| 4 | Dev Mehta | AR |
| 5 | Arjun Nair | WK |
GameweekPoints
| player_id | gameweek | points |
|---|---|---|
| 1 | 1 | 12 |
| 1 | 2 | 88 |
| 2 | 1 | 31 |
| 2 | 2 | 40 |
| 3 | 1 | 25 |
| 3 | 2 | 2 |
| 3 | 3 | 61 |
| 4 | 1 | 45 |
Output
| role | scores | median_points |
|---|---|---|
| AR | 1 | 45 |
| BAT | 4 | 35.5 |
| BWL | 3 | 25 |
How to solve Median Fantasy Points per Gameweek by Player Role
SQL has no portable MEDIAN, so we find the middle positions ourselves. After joining each score to its player's role, two windows over the same partition do the work: ROW_NUMBER() numbers a role's scores in ascending order, and COUNT(*) OVER (PARTITION BY role) puts the role's total beside every row. The tie-breakers in the ORDER BY (player_id, gameweek) only make the numbering repeatable — equal scores are interchangeable for a median.
For n scores the middle is at positions FLOOR((n + 1) / 2) and FLOOR(n / 2) + 1. For odd n both are the same position; for even n they are the two middle ones. Keeping those rows and averaging gives the median in both cases, rounded once at the end. In the example BAT has four scores (12, 31, 40, 88), so the median is (31 + 40) / 2 = 35.5.
The alternative is the classic counting definition: a value is a middle value when at least half the scores are <= it and at least half are >= it; averaging the distinct such values gives the median. It needs correlated counts — quadratic — where the window version is one sort per role.
Reference solution (MySQL)
WITH ordered AS (
SELECT p.role, g.points,
ROW_NUMBER() OVER (PARTITION BY p.role ORDER BY g.points, g.player_id, g.gameweek) AS rn,
COUNT(*) OVER (PARTITION BY p.role) AS cnt
FROM GameweekPoints g
JOIN FantasyPlayer p ON p.player_id = g.player_id
)
SELECT role, MAX(cnt) AS scores, ROUND(AVG(points), 1) AS median_points
FROM ordered
WHERE rn IN (FLOOR((cnt + 1) / 2), FLOOR(cnt / 2) + 1)
GROUP BY role
ORDER BY roleAnother way
WITH s AS (SELECT p.role, g.points FROM GameweekPoints g JOIN FantasyPlayer p ON p.player_id = g.player_id)
SELECT a.role, (SELECT COUNT(*) FROM s c WHERE c.role = a.role) AS scores, ROUND(AVG(DISTINCT a.points), 1) AS median_points
FROM s a
WHERE (SELECT COUNT(*) FROM s b WHERE b.role = a.role AND b.points <= a.points) >= (SELECT COUNT(*) FROM s c WHERE c.role = a.role) / 2
AND (SELECT COUNT(*) FROM s b WHERE b.role = a.role AND b.points >= a.points) >= (SELECT COUNT(*) FROM s c WHERE c.role = a.role) / 2
GROUP BY a.role
ORDER BY a.role← The Over in Which Each T20 Innings Passed 100 Runs · Fantasy App Signup Cohorts Retained Into the Next Month →