Skip to content

Instantly share code, notes, and snippets.

@twidi

twidi/score.sql

Last active September 16, 2022 23:07
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save twidi/2f8d6d3b11aa01d94da0034b9ec8d84d to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save twidi/2f8d6d3b11aa01d94da0034b9ec8d84d to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. twidi revised this gist Sep 16, 2022. 1 changed file with 2 additions and 0 deletions.
    2 changes: 2 additions & 0 deletions score.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -95,6 +95,8 @@ 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 ,
  2. twidi revised this gist Sep 16, 2022. 1 changed file with 0 additions and 1 deletion.
    1 change: 0 additions & 1 deletion score.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -95,7 +95,6 @@ from aggregated
    order by nb_missing_games, final_score desc;
    $$LANGUAGE sql IMMUTABLE;

    select * from pg_temp.get_scores(0);
    select
    "user",
    round(last.final_score::numeric, 1) as final_score_last ,
  3. twidi revised this gist Sep 16, 2022. 1 changed file with 46 additions and 8 deletions.
    54 changes: 46 additions & 8 deletions score.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -1,3 +1,16 @@
    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,
    @@ -8,8 +21,9 @@ with
    having count(*) >= 2
    order by game_id),
    -- get the last day with a played game
    last_play_day as (select max(day) - interval '1 day' as last_day_with_game -- '1 day' to get values from preview played day
    from _games),
    _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
    @@ -18,11 +32,13 @@ with
    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),
    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,
    @@ -63,9 +79,31 @@ with
    from players p
    inner join scores using ("user")) t)

    -- use select * to see all intermediary data that is useful to see
    select "user",
    nb_missing_games,
    round(final_score::numeric, 1)
    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;
    order by nb_missing_games, final_score desc;
    $$LANGUAGE sql IMMUTABLE;

    select * from pg_temp.get_scores(0);
    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");
  4. twidi revised this gist Sep 16, 2022. 1 changed file with 7 additions and 5 deletions.
    12 changes: 7 additions & 5 deletions score.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -7,13 +7,15 @@ with
    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) - interval '1 day' as last_day_with_game -- '1 day' to get values from preview played day
    from _games),
    -- 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),
    -- get the last day with a played game
    last_play_day as (select max(day) as last_day_with_game
    from _games),
    -- for each game, compute the number of days with games between this game and the last one
    games as (select _games.*,
    (select count(*)
    @@ -33,7 +35,7 @@ with
    from game_score
    inner join games using (game_id)),
    scores as (
    select * from _scores where game_reverse_number <= 100
    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",
    @@ -66,4 +68,4 @@ select "user",
    nb_missing_games,
    round(final_score::numeric, 1)
    from aggregated
    order by nb_missing_games, final_score desc;
    order by nb_missing_games, final_score desc;
  5. twidi revised this gist Sep 16, 2022. 1 changed file with 5 additions and 3 deletions.
    8 changes: 5 additions & 3 deletions score.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -23,7 +23,7 @@ with
    cross join last_play_day),
    -- 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,
    _scores as (select game_id,
    day,
    nb_players,
    "user",
    @@ -32,14 +32,16 @@ with
    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 <= 100
    ),
    -- 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
    where game_reverse_number <= 100
    group by "user"),
    -- for each player, get the number of expected and missing days
    players as (select p.*,
    @@ -60,7 +62,7 @@ with
    inner join scores using ("user")) t)

    -- use select * to see all intermediary data that is useful to see
    select "user",
    select "user",
    nb_missing_games,
    round(final_score::numeric, 1)
    from aggregated
  6. twidi revised this gist Sep 16, 2022. 1 changed file with 2 additions and 1 deletion.
    3 changes: 2 additions & 1 deletion score.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -60,7 +60,8 @@ with
    inner join scores using ("user")) t)

    -- use select * to see all intermediary data that is useful to see
    select nb_missing_games,
    select "user",
    nb_missing_games,
    round(final_score::numeric, 1)
    from aggregated
    order by nb_missing_games, final_score desc;
  7. twidi created this gist Sep 16, 2022.
    66 changes: 66 additions & 0 deletions score.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,66 @@
    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 list of all days with games
    play_days as (select distinct(day)
    from _games
    order by day),
    -- get the last day with a played game
    last_play_day as (select max(day) as last_day_with_game
    from _games),
    -- for each game, compute the number of days with games between this game and the last one
    games as (select _games.*,
    (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),
    -- 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)),
    -- 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
    where game_reverse_number <= 100
    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)

    -- use select * to see all intermediary data that is useful to see
    select nb_missing_games,
    round(final_score::numeric, 1)
    from aggregated
    order by nb_missing_games, final_score desc;