Add script to refresh the active players MV.
[xonotic/xonstatdb.git] / scripts / refresh_active_players_mv.sql
1 begin;
2         delete from active_players_mv;
3
4         insert into active_players_mv
5         select row_number() over(order by sum(pgs.alivetime) desc) sort_order, p.player_id, p.nick, sum(pgs.alivetime) alivetime
6         from players p join player_game_stats pgs on p.player_id = pgs.player_id
7         where pgs.create_dt >= (now() at time zone 'UTC' - interval '1 week')
8         and p.active_ind = true
9         and p.player_id > 2
10         group by 2, 3;
11 end;