Dream11: Each Player's Form Over Their Last Three Matches

Medium
639.

Dream11: Each Player's Form Over Their Last Three Matches

Dream11's team-building tips show each player's recent form: their average fantasy points in their three most recent matches.

For every player with at least three matches in PlayerPoints, return player_name, player_role and form_avg, the average of their fantasy points in their three latest matches by match_date, rounded to 2 decimals. Players with fewer than three matches are left out. Order by form_avg descending, then by player_name.

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

ColumnTypeValues
player_idint
player_namevarchar
player_roleenumWK, BAT, AR, BOWL

Primary key: player_id.

Player names are unique.

Table: PlayerPoints

ColumnType
player_idint
match_idint
match_datedate
fantasy_pointsdecimal

Primary key (together): player_id, match_id.

Fantasy points a player scored in a match (steps of 0.5; can be negative). A player plays at most one match on a date.

Example

The rows must come back in the order shown.

Player

player_idplayer_nameplayer_role
1Arjun RaoBAT
2Kabir KhanBOWL
3Rohan IyerAR
4Dev NairWK

PlayerPoints

player_idmatch_idmatch_datefantasy_points
15012025-04-0112
15022025-04-0488.5
15032025-04-0740
15042025-04-1061
25012025-04-0145
25032025-04-07-2
25052025-04-1270
35022025-04-0433
35042025-04-1020
45022025-04-0460
2 more rows

Output

player_nameplayer_roleform_avg
Arjun RaoBAT63.17
Dev NairWK54.17
Kabir KhanBOWL37.67
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: Each Player's Form Over Their Last Three Matches

Dream11's team-building tips show each player's recent form: their average fantasy points in their three most recent matches.

  • Difficulty: Medium
  • Topics: Window Functions, Aggregation
  • Dialect: MySQL
  • Problem: #639

Problem statement

Dream11's team-building tips show each player's recent form: their average fantasy points in their three most recent matches.

For every player with at least three matches in PlayerPoints, return player_name, player_role and form_avg, the average of their fantasy points in their three latest matches by match_date, rounded to 2 decimals. Players with fewer than three matches are left out. Order by form_avg descending, then by player_name.

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

ColumnType
player_idint
player_namevarchar
player_roleenum(WK, BAT, AR, BOWL)

Primary key: player_id.

Player names are unique.

Table: PlayerPoints

ColumnType
player_idint
match_idint
match_datedate
fantasy_pointsdecimal

Primary key: player_id, match_id.

Fantasy points a player scored in a match (steps of 0.5; can be negative). A player plays at most one match on a date.

Examples

Example 1

Player

player_idplayer_nameplayer_role
1Arjun RaoBAT
2Kabir KhanBOWL
3Rohan IyerAR
4Dev NairWK

PlayerPoints

player_idmatch_idmatch_datefantasy_points
15012025-04-0112
15022025-04-0488.5
15032025-04-0740
15042025-04-1061
25012025-04-0145
25032025-04-07-2
25052025-04-1270
35022025-04-0433
35042025-04-1020
45022025-04-0460
45032025-04-0746.5
45042025-04-1056

Output

player_nameplayer_roleform_avg
Arjun RaoBAT63.17
Dev NairWK54.17
Kabir KhanBOWL37.67

How to solve Dream11: Each Player's Form Over Their Last Three Matches

"The last three" is a top-N-per-group question. ROW_NUMBER() OVER (PARTITION BY player_id ORDER BY match_date DESC) numbers each player's matches from the latest backwards; keeping k <= 3 leaves at most three rows per player, and AVG over them is the form figure. A player plays at most one match per date, so the numbering has no ties to break.

The filter k <= 3 alone would still let a player with one or two matches through, averaged over fewer games. HAVING COUNT(*) = 3 removes them — after the filter a group has three rows exactly when the player had at least three matches. Points move in steps of 0.5, so an average of three is a multiple of 1/6 and rounding it to two decimals never lands on a half.

The alternative avoids window functions: a match is among a player's last three when fewer than three of their matches are later, and a second correlated count enforces the minimum; dividing the sum by 3 is then the average. It is quadratic per player, the window version a sort per player.

Reference solution (MySQL)

WITH recent AS (
  SELECT player_id, fantasy_points,
         ROW_NUMBER() OVER (PARTITION BY player_id ORDER BY match_date DESC) AS k
  FROM PlayerPoints
)
SELECT p.player_name, p.player_role, ROUND(AVG(r.fantasy_points), 2) AS form_avg
FROM recent r
JOIN Player p ON p.player_id = r.player_id
WHERE r.k <= 3
GROUP BY p.player_id, p.player_name, p.player_role
HAVING COUNT(*) = 3
ORDER BY form_avg DESC, p.player_name

Another way

SELECT p.player_name, p.player_role, ROUND(SUM(a.fantasy_points) / 3, 2) AS form_avg
FROM PlayerPoints a JOIN Player p ON p.player_id = a.player_id
WHERE (SELECT COUNT(*) FROM PlayerPoints b WHERE b.player_id = a.player_id AND b.match_date > a.match_date) < 3
  AND (SELECT COUNT(*) FROM PlayerPoints c WHERE c.player_id = a.player_id) >= 3
GROUP BY p.player_id, p.player_name, p.player_role
ORDER BY form_avg DESC, p.player_name

← Dream11: Withdrawal Requests Made Before KYC Approval · Dream11: Multiple Accounts Behind One Gmail Address →