I've adapted the recursive query from here in order to calculate ELO ratings (like in chess) from a list of game outcomes on-the-fly.
Given a table of games like:
id | home_player | away_player | home_score | away_score |
|--|-------------|-------------|------------|------------|
1 | 1 | 2 | 21 | 18 |
2 | 2 | 3 | 14 | 21 |
3 | 1 | 3 | 12 | 21 |
5 | 3 | 2 | 21 | 8 |
I want to construct a table of ELO ratings for each player after each game has been played, like this:
| current_game_number | player_id | previous_elo | new_elo |
|---------------------|-----------|--------------|---------|
| 0 | 1 | 1000 | 1000 |
| 0 | 2 | 1000 | 1000 |
| 0 | 3 | 1000 | 1000 |
| 1 | 3 | 1000 | 1000 |
| 1 | 2 | 1000 | 984 |
| 1 | 1 | 1000 | 1016 |
| 2 | 1 | 1016 | 1016 |
| 2 | 2 | 984 | 969 |
| 2 | 3 | 1000 | 1015 |
| 3 | 3 | 1015 | 1031 |
| 3 | 2 | 969 | 969 |
| 3 | 1 | 1016 | 1000 |
I can get most of the way there with this recursive query:
WITH RECURSIVE p(current_game_number) AS (
SELECT
0 AS id,
id as player_id,
1000.0 :: FLOAT AS previous_elo,
1000.0 :: FLOAT AS new_elo
FROM players
UNION ALL
(
WITH previous_elos AS (
SELECT *
FROM p
)
SELECT
games.id,
player_id,
previous_elos.new_elo AS previous_elo,
round(CASE WHEN player_id NOT IN (home_player, away_player)
THEN previous_elos.new_elo
WHEN player_id = home_player
THEN previous_elos.new_elo + 32.0 * ((CASE WHEN home_score > away_score THEN 1 ELSE 0 END) - (r1 / (r1 + r2)))
ELSE previous_elos.new_elo + 32.0 * ((CASE WHEN away_score > home_score THEN 1 ELSE 0 END) - (r2 / (r1 + r2))) END)
FROM games
JOIN previous_elos
ON current_game_number = games.id - 1
JOIN LATERAL (
SELECT
pow(10.0, (SELECT new_elo
FROM previous_elos
WHERE current_game_number = games.id - 1 AND player_id = home_player) / 400.0) AS r1,
pow(10.0, (SELECT new_elo
FROM previous_elos
WHERE current_game_number = games.id - 1 AND player_id = away_player) / 400.0) AS r2
) r
ON TRUE
)
) SELECT * FROM p;
However, If there is a gap in the game ID sequence, as in the example table above, the query stops at the gap and doesn't continue to calculate ELOs. I would like to support gaps so that games can be deleted and concurrent inserts are supported.
I'm lost as to how to extend the query to "skip gaps" in the id sequence. My hunch is it would require changing the recursive join to skip the gaps:
FROM games
JOIN previous_elos
ON current_game_number = games.id - 1
Does anyone have a suggestion to fix this? Any help is very appreciated!
Please see this SQLFiddle which is populated with the schema and sample data.
Best Answer
To avoid gaps use additional CTE: