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
| Column | Type |
|---|---|
| player_id | int |
| player_name | varchar |
| player_role | enum(WK, BAT, AR, BOWL) |
Primary key: player_id.
Player names are unique.
Table: PlayerPoints
| Column | Type |
|---|---|
| player_id | int |
| match_id | int |
| match_date | date |
| fantasy_points | decimal |
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_id | player_name | player_role |
|---|---|---|
| 1 | Arjun Rao | BAT |
| 2 | Kabir Khan | BOWL |
| 3 | Rohan Iyer | AR |
| 4 | Dev Nair | WK |
PlayerPoints
| player_id | match_id | match_date | fantasy_points |
|---|---|---|---|
| 1 | 501 | 2025-04-01 | 12 |
| 1 | 502 | 2025-04-04 | 88.5 |
| 1 | 503 | 2025-04-07 | 40 |
| 1 | 504 | 2025-04-10 | 61 |
| 2 | 501 | 2025-04-01 | 45 |
| 2 | 503 | 2025-04-07 | -2 |
| 2 | 505 | 2025-04-12 | 70 |
| 3 | 502 | 2025-04-04 | 33 |
| 3 | 504 | 2025-04-10 | 20 |
| 4 | 502 | 2025-04-04 | 60 |
| 4 | 503 | 2025-04-07 | 46.5 |
| 4 | 504 | 2025-04-10 | 56 |
Output
| player_name | player_role | form_avg |
|---|---|---|
| Arjun Rao | BAT | 63.17 |
| Dev Nair | WK | 54.17 |
| Kabir Khan | BOWL | 37.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_nameAnother 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 →