Median Fantasy Points per Gameweek by Player Role

Hard
469.

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

ColumnTypeValues
player_idint
player_namevarchar
roleenumAR, BAT, BWL, WK

Primary key: player_id.

One row per player in the game.

Table: GameweekPoints

ColumnType
player_idint
gameweekint
pointsint

Primary key (together): player_id, gameweek.

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

Example

The rows must come back in the order shown.

FantasyPlayer

player_idplayer_namerole
1Rohan IyerBAT
2Kabir KhanBAT
3Aarav DasBWL
4Dev MehtaAR
5Arjun NairWK

GameweekPoints

player_idgameweekpoints
1112
1288
2131
2240
3125
322
3361
4145

Output

rolescoresmedian_points
AR145
BAT435.5
BWL325
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.

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

ColumnType
player_idint
player_namevarchar
roleenum(AR, BAT, BWL, WK)

Primary key: player_id.

One row per player in the game.

Table: GameweekPoints

ColumnType
player_idint
gameweekint
pointsint

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_idplayer_namerole
1Rohan IyerBAT
2Kabir KhanBAT
3Aarav DasBWL
4Dev MehtaAR
5Arjun NairWK

GameweekPoints

player_idgameweekpoints
1112
1288
2131
2240
3125
322
3361
4145

Output

rolescoresmedian_points
AR145
BAT435.5
BWL325

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 role

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