recursive.sql

WITH RECURSIVE p(current_game_number) AS
  (WITH players AS (SELECT DISTINCT player_name FROM players) 
  
   SELECT (select min(id) from plays) AS game_number, player_name, 1000.0 AS elo
   FROM players
   UNION ALL
     (WITH previous_elos AS (SELECT * FROM p) 
     	SELECT plays.id,
                        player_name,
                        CASE
                            WHEN player_name NOT IN (plays."right", plays."left") THEN previous_elos.elo
                            WHEN player_name = plays."right" THEN 
                            	previous_elos.elo + 32.0 * ((plays.chosen = plays."right")::int + ((plays.chosen = 'none')::int)*.5 - (r1 / (r1 + r2)))
                            ELSE 
                            	previous_elos.elo + 32.0 * ((plays.chosen = plays."left")::int + ((plays.chosen = 'none')::int)*.5 - (r2 / (r1 + r2)))
                        END
      FROM plays
      JOIN previous_elos ON current_game_number = plays.id - 1
      JOIN LATERAL
        (SELECT pow(10.0, previous_elos.elo / 400.0) AS r1,
                pow(10.0, previous_elos.elo / 400.0) AS r2) r ON TRUE
                ))
select * from (select distinct on(player_name)
player_name, last_value(elo) OVER (PARTITION BY player_name ORDER BY current_game_number desc) as rating
from p) results order by rating desc;

adopted from https://elliot.land/post/elo-rating-system-implemented-in-single-sql-select