Elo Rating System Implemented in Single SQL SELECT

The Elo rating system is a method for calculating the relative skill levels of players in zero-sum games such as chess. However, it has been applied to many other sports and competitions.

Put simply there is a bunch of reasons why Elo is great:

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.

New players can be added at any time but they should always have the same starting rating. I have chosen 1000 for this value.

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:

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. 😢

Originally published at http://elliot.land on April 30, 2018.