scripts/refresh_summary_stats_mv.sql

   1 begin;
   2         delete from summary_stats_mv;
   3
   4         insert into summary_stats_mv
   5         -- all time
   6         (with active_players as (
   7                 select count(distinct player_id) num_players
   8                 from player_game_stats pgs
   9                 where pgs.player_id > 2
  10         )
  11         select
  12           'all' scope,
  13           row_number() over (order by count(*) desc) rn,
  14           ap.num_players,
  15           g.game_type_cd,
  16           count(*)
  17         from games g cross join active_players ap
  18         group by scope, ap.num_players, g.game_type_cd)
  19
  20         -- daily
  21         UNION
  22         (with active_players as (
  23                 select count(distinct player_id) num_players
  24                 from player_game_stats pgs
  25                 where pgs.player_id > 2
  26                 and pgs.create_dt >= now() at time zone 'utc' - interval '1 day'
  27         )
  28         select
  29           'day' scope,
  30           row_number() over (order by count(*) desc) rn,
  31           ap.num_players,
  32           g.game_type_cd,
  33           count(*)
  34         from games g cross join active_players ap
  35         where g.create_dt >= now() at time zone 'utc' - interval '1 day'
  36         group by scope, ap.num_players, g.game_type_cd);
  37
  38 end;