# 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:

- 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.
- 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.
- 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).
- 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*x*is 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

*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

*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:

- The above SQL was designed to work with PostgreSQL.
- It should be possible to adapt this to other SQL databases that also support recursive CTEs.
- 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. - 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.*