1 -- This transaction and corresponding anonymous block will update the
2 -- player_agg_stats_mv table. Change the values of "since" and "until" below to
3 -- control the window of players you want to update.
8 since timestamp without time zone := now() at time zone 'UTC' - interval '4 weeks';
9 until timestamp without time zone := now() at time zone 'UTC';
11 -- clear the slots for the upcoming records
13 from player_agg_stats_mv
15 select distinct player_id
16 from player_game_stats
18 and create_dt between since and until
21 insert into player_agg_stats_mv
24 agg_stats.game_type_cd game_type_cd,
27 max(agg_stats.create_dt) last_seen,
28 coalesce(sum(win) + sum(loss), 0) games,
29 coalesce(sum(win), 0) wins,
30 coalesce(sum(loss), 0) losses,
31 coalesce(sum(kills), 0) kills,
32 coalesce(sum(deaths), 0) deaths,
33 coalesce(sum(suicides), 0) suicides,
34 coalesce(sum(captures), 0) captures,
35 coalesce(sum(pickups), 0) pickups,
36 coalesce(sum(drops), 0) drops,
37 coalesce(sum(carrier_frags), 0) carrier_frags,
38 coalesce(round(sum(alivetime)/60), 0) alivetime
46 when g.winner = pgs.team then 1
47 when pgs.scoreboardpos = 1 then 1
51 when g.winner = pgs.team then 0
52 when pgs.scoreboardpos = 1 then 0
62 extract(epoch from pgs.alivetime) alivetime
67 g.game_id = pgs.game_id
69 and pgs.player_id in (
70 select distinct player_id
71 from player_game_stats
73 and create_dt between since and until
78 on p.player_id = agg_stats.player_id
83 agg_stats.game_type_cd
86 exception when others then
87 raise notice 'something went wrong';