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

Now we can execute the following SQL:

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.

Written by

Written by