Elo Rating System Implemented in Single SQL SELECT

  1. The “zero-sum” means that points are never introduced to the system by competition alone. Any increase or decrease in a players Elo rating is reflected as the inverse in the other players rating. Points are only introduced when a new player enters the competition. All players will (and must) start with the same initial rating, like 1000.
  2. The magnitude of the rating change of a winning and losing player is proportional to the rating (the skill level) of the opponent. This means that a high skilled player cannot simply win lots of game against lesser skilled opponents to gain points.
  3. It is not affected by the number of games that an opponent has played. This means that new players can be introduced at any time and the relative rating of a player with 10 games will be fair against another player with 1000 games (assuming their skill level is the same).
  4. The Elo rating is calculated in O(1) time. You only need to know the current rating of each opponent and who won (or drew) in a match to calculate both of their new respective Elo ratings.

How Do We Calculate It?

Since we are calculating a new Elo score for each player the two equations are:

  • Rx is the current rating of the player, where xis the player number. The tick above Rx means that it is the new rating for that player.
  • K is a constant that determines how far the ratings can skew. I have chosen 32. You can use any number as long as all of your Elo ratings are calculated with the same value of K.
  • Sx is the score of each of the respective players. The score should be 1 or 0. However, it is also possible to give both players a score of 0.5 for a draw. The important thing is that S1+ S2= 1.

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.

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.

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);
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;
  1. The above SQL was designed to work with PostgreSQL.
  2. It should be possible to adapt this to other SQL databases that also support recursive CTEs.
  3. The JOIN LATERAL is not strictly necessary. You could substitute the expressions of r1 and r2 into their respective expressions above, it would just be more verbose.
  4. It really saddens me to say that this does not work with SQLite3 because SQLite3 does not have a POW() function. 😢

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Elliot Chance

Elliot Chance

581 Followers

I’m a data nerd and TDD enthusiast originally from Sydney. Currently working for Uber in New York. My thoughts here are my own. 🤓 elliotchance@gmail.com