scripts/update_summary_stats.sql

   1 begin transaction;
   2     -- get rid of the existing summary stats since we're about to refresh
   3     delete from summary_stats;
   4
   5     insert into summary_stats(total_players, total_servers, total_games,
   6                               total_dm_games, total_duel_games, total_ctf_games)
   7     with total_games as (
   8        select game_type_cd, count(*) total_games
   9        from games
  10        where game_type_cd in ('duel', 'dm', 'ctf')
  11        group by game_type_cd
  12     ),
  13     total_players as (
  14        select count(*) total_players
  15        from players
  16        where active_ind = true
  17     ),
  18     total_servers as (
  19     select count(*) total_servers
  20        from servers
  21        where active_ind = true
  22     )
  23     select tp.total_players, ts.total_servers, dm.total_games+duel.total_games+ctf.total_games total_games,
  24            dm.total_games dm_games, duel.total_games duel_games, ctf.total_games ctf_games
  25     from   total_games dm, total_games duel, total_games ctf, total_players tp, total_servers ts
  26     where  dm.game_type_cd = 'dm'
  27     and    ctf.game_type_cd = 'ctf'
  28     and    duel.game_type_cd = 'duel';
  29
  30 end;