Swoosh? Boink! (scoring)

score.sql

CREATE OR REPLACE FUNCTION pg_temp.get_scores(skip_days int)  RETURNS TABLE (
    "user" varchar(60),
    first_game_id int,
    nb_games int,
    first_game_day date,
    nb_played_days int,
    nb_expected_days int,
    nb_missing_days int,
    final_score float,
    regularity_factor float,
    averaged_score float,
    nb_missing_games int
) AS $$
with
    -- keep only games with at least two players, and for each kept game, compute the day and the number of players
    _games as (select game_id,
                      min("date")::TIMESTAMP::DATE as day,
                      count(*)                     as nb_players
               from game_score
               group by game_id
               having count(*) >= 2
               order by game_id),
    -- get the last day with a played game
    _last_play_day as (select max(day) as last_day_with_game from _games),
    last_play_day as (select last_day_with_game - (skip_days ||' DAYS')::interval  as last_day_with_game
                      from _last_play_day),
    -- get the list of all days with games
    play_days as (select distinct(day)
                  from _games
                    cross join last_play_day
                  where day <= last_day_with_game
                  order by day),
    -- for each game, compute the number of days with games between this game and the last one
    games as (select _games.*,
                     last_day_with_game,
                     (select count(*)
                      from play_days
                      where day between _games.day and last_day_with_game) as nb_expected_days
              from _games
                       cross join last_play_day
              where day <= last_day_with_game),
    -- on all kept games, compute the scores adjusted regarding the number of players
    -- and number games for each players, starting from 1 for the most recent one
    _scores as (select game_id,
                      day,
                      nb_players,
                      "user",
                      score,
                      score * (1 + log(nb_players - 1))                             as score_with_players_factor,
                      row_number() over (partition by "user" order by game_id desc) as game_reverse_number
               from game_score
                        inner join games using (game_id)),
    scores as (
        select * from _scores where game_reverse_number <= 250
    ),
    -- list players with their oldest game, limiting to the 100 last games, and the number of played games and played days
    _players as (select "user",
                        min(game_id)          as first_game_id,
                        count(*)              as nb_games,
                        min(day)              as first_game_day,
                        count(distinct (day)) as nb_played_days
                 from scores
                 group by "user"),
    -- for each player, get the number of expected and missing days
    players as (select p.*,
                       nb_expected_days,
                       nb_expected_days - nb_played_days as nb_missing_days
                from _players p
                         inner join games g on p.first_game_id = g.game_id),
    -- compute aggregated data for every player, and the final score
    aggregated as (select *, averaged_score * regularity_factor as final_score
                   from (select distinct on ("user") p.*,
                                                     0.95 ^ nb_missing_days            as regularity_factor,
                                                     (avg(case score_with_players_factor
                                                              when 0 then 0.0
                                                              else 1000 + (score_with_players_factor + 1) ^ 2 end)
                                                      over (partition by "user") / 10) as averaged_score,
                                                     greatest(0, 25 - nb_games)        as nb_missing_games
                         from players p
                                  inner join scores using ("user")) t)

select
    "user",
    first_game_id,
    nb_games,
    first_game_day,
    nb_played_days,
    nb_expected_days,
    nb_missing_days,
    final_score,
    regularity_factor,
    averaged_score,
    nb_missing_games
from aggregated
order by nb_missing_games, final_score desc;
$$LANGUAGE sql IMMUTABLE;


-- select * from pg_temp.get_scores(0) last, pg_temp.get_scores(1) previous; -- use this to see all columns
select
    "user",
    round(last.final_score::numeric, 1) as final_score_last ,
    last.nb_missing_games as nb_missing_games_last,
    round(previous.final_score::numeric, 1) as final_score_previous ,
    previous.nb_missing_games as nb_missing_games_previous

from
    pg_temp.get_scores(0) last
    join pg_temp.get_scores(1) previous
    using("user");