As an example, lets say Bob with a rating of 1400 loses to Jane with a rating of 1100:

The scores can be rounded to a whole number to look neater.

The Sane Way to Do It

Since the only data required to calculate the new Elo ratings is to know the current player ratings you can store this information in a database or the like and update it with each match.

But that’s no fun!

The Less-Than-Sane Way to Do It

What if we had a requirement to calculate all Elo ratings for all plays using the entire history of the matches… and we could only do it with a single SELECT . Is it possible? Well, it is! With a RECURSIVE SELECT .

First let’s start with the match history. Here is a table that holds the history of the matches, and some test data:

CREATE TABLE plays (

game_number INT NOT NULL, -- must be consecutive and start at 1!

p1name VARCHAR(255) NOT NULL, -- first player

p1score FLOAT NOT NULL, -- 0 or 1

p2name VARCHAR(255) NOT NULL, -- second player

p2score FLOAT NOT NULL -- 0 or 1

); INSERT INTO plays VALUES

(1, 'Elliot', 0, 'Brendan', 1),

(2, 'Bob', 1, 'Brendan', 0),

(3, 'Bob', 1, 'Elliot', 0),

(4, 'Jane', 1, 'Bob', 0),

(5, 'Bob', 0, 'Brendan', 1),

(6, 'Jane', 1, 'Elliot', 0);

Now we can execute the following SQL:

WITH RECURSIVE p(current_game_number) AS (

WITH players AS (

SELECT DISTINCT p1name AS player_name

FROM plays

UNION

SELECT DISTINCT p2name

FROM plays

)

SELECT

0 AS game_number,

player_name,

1000.0 :: FLOAT AS previous_elo,

1000.0 :: FLOAT AS new_elo

FROM players

UNION ALL

(

WITH previous_elos AS (

SELECT *

FROM p

)

SELECT

plays.game_number,

player_name,

previous_elos.new_elo AS previous_elo,

round(CASE WHEN player_name NOT IN (p1name, p2name)

THEN previous_elos.new_elo

WHEN player_name = p1name

THEN previous_elos.new_elo + 32.0 * (p1score - (r1 / (r1 + r2)))

ELSE previous_elos.new_elo + 32.0 * (p2score - (r2 / (r1 + r2))) END)

FROM plays

JOIN previous_elos

ON current_game_number = plays.game_number - 1

JOIN LATERAL (

SELECT

pow(10.0, (SELECT new_elo

FROM previous_elos

WHERE current_game_number = plays.game_number - 1 AND player_name = p1name) / 400.0) AS r1,

pow(10.0, (SELECT new_elo

FROM previous_elos

WHERE current_game_number = plays.game_number - 1 AND player_name = p2name) / 400.0) AS r2

) r

ON TRUE

)

)

SELECT

player_name,

(

SELECT new_elo

FROM p

WHERE t.player_name = p.player_name

ORDER BY current_game_number DESC

LIMIT 1

) AS elo,

count(CASE WHEN previous_elo < new_elo

THEN 1

ELSE NULL END) AS wins,

count(CASE WHEN previous_elo > new_elo

THEN 1

ELSE NULL END) AS losses

FROM

(

SELECT *

FROM p

WHERE previous_elo <> new_elo

ORDER BY current_game_number, player_name

) t

GROUP BY player_name

ORDER BY elo DESC;

And hoorah! (The wins and losses are just a little bonus):

Some final notes: