r6db
r6db copied to clipboard
r6db
Community stats
That has always been the goal of what we have wanted to do. Idea is to have something similar to the progressions table, except everything is averaged amongst all users. From there we can calculate specific operator wlr, kdr trends etc.
without order or actual thought, here are some things we can calculate:
- average w/l (or k/d, etc) across different ranks or regions
- operator distribution (playtime, w/l, etc)
- (in theory) compare a player with the 'average'
Should I sum all of the different operator stats or should I average some (like K/D)
This should do the trick
INSERT INTO community_operators (relevant_at, inserted_at, updated_at, operators)
SELECT '2017-09-18', NOW(), NOW(), row_to_json(y) FROM
(SELECT
AVG(iq_time_played_delta) AS iq_time_played_delta,
SUM(iq_time_played) AS iq_time_played,
AVG(CASE WHEN iq_time_played_delta > 0 THEN iq_kills_delta END) AS iq_kills_delta,
SUM(iq_kills) AS iq_kills,
AVG(CASE WHEN iq_time_played_delta > 0 THEN iq_deaths_delta END) AS iq_deaths_delta,
SUM(iq_deaths) AS iq_deaths,
AVG(CASE WHEN iq_time_played_delta > 0 THEN iq_won_delta END) AS iq_won_delta,
SUM(iq_won) AS iq_won,
AVG(CASE WHEN iq_time_played_delta > 0 THEN iq_lost_delta END) AS iq_lost_delta,
SUM(iq_lost) AS iq_lost,
AVG(ash_time_played_delta) AS ash_time_played_delta,
SUM(ash_time_played) AS ash_time_played,
AVG(CASE WHEN ash_time_played_delta > 0 THEN ash_kills_delta END) AS ash_kills_delta,
SUM(ash_kills) AS ash_kills,
AVG(CASE WHEN ash_time_played_delta > 0 THEN ash_deaths_delta END) AS ash_deaths_delta,
SUM(ash_deaths) AS ash_deaths,
AVG(CASE WHEN ash_time_played_delta > 0 THEN ash_won_delta END) AS ash_won_delta,
SUM(ash_won) AS ash_won,
AVG(CASE WHEN ash_time_played_delta > 0 THEN ash_lost_delta END) AS ash_lost_delta,
SUM(ash_lost) AS ash_lost,
AVG(doc_time_played_delta) AS doc_time_played_delta,
SUM(doc_time_played) AS doc_time_played,
AVG(CASE WHEN doc_time_played_delta > 0 THEN doc_kills_delta END) AS doc_kills_delta,
SUM(doc_kills) AS doc_kills,
AVG(CASE WHEN doc_time_played_delta > 0 THEN doc_deaths_delta END) AS doc_deaths_delta,
SUM(doc_deaths) AS doc_deaths,
AVG(CASE WHEN doc_time_played_delta > 0 THEN doc_won_delta END) AS doc_won_delta,
SUM(doc_won) AS doc_won,
AVG(CASE WHEN doc_time_played_delta > 0 THEN doc_lost_delta END) AS doc_lost_delta,
SUM(doc_lost) AS doc_lost,
AVG(buck_time_played_delta) AS buck_time_played_delta,
SUM(buck_time_played) AS buck_time_played,
AVG(CASE WHEN buck_time_played_delta > 0 THEN buck_kills_delta END) AS buck_kills_delta,
SUM(buck_kills) AS buck_kills,
AVG(CASE WHEN buck_time_played_delta > 0 THEN buck_deaths_delta END) AS buck_deaths_delta,
SUM(buck_deaths) AS buck_deaths,
AVG(CASE WHEN buck_time_played_delta > 0 THEN buck_won_delta END) AS buck_won_delta,
SUM(buck_won) AS buck_won,
AVG(CASE WHEN buck_time_played_delta > 0 THEN buck_lost_delta END) AS buck_lost_delta,
SUM(buck_lost) AS buck_lost,
AVG(echo_time_played_delta) AS echo_time_played_delta,
SUM(echo_time_played) AS echo_time_played,
AVG(CASE WHEN echo_time_played_delta > 0 THEN echo_kills_delta END) AS echo_kills_delta,
SUM(echo_kills) AS echo_kills,
AVG(CASE WHEN echo_time_played_delta > 0 THEN echo_deaths_delta END) AS echo_deaths_delta,
SUM(echo_deaths) AS echo_deaths,
AVG(CASE WHEN echo_time_played_delta > 0 THEN echo_won_delta END) AS echo_won_delta,
SUM(echo_won) AS echo_won,
AVG(CASE WHEN echo_time_played_delta > 0 THEN echo_lost_delta END) AS echo_lost_delta,
SUM(echo_lost) AS echo_lost,
AVG(fuze_time_played_delta) AS fuze_time_played_delta,
SUM(fuze_time_played) AS fuze_time_played,
AVG(CASE WHEN fuze_time_played_delta > 0 THEN fuze_kills_delta END) AS fuze_kills_delta,
SUM(fuze_kills) AS fuze_kills,
AVG(CASE WHEN fuze_time_played_delta > 0 THEN fuze_deaths_delta END) AS fuze_deaths_delta,
SUM(fuze_deaths) AS fuze_deaths,
AVG(CASE WHEN fuze_time_played_delta > 0 THEN fuze_won_delta END) AS fuze_won_delta,
SUM(fuze_won) AS fuze_won,
AVG(CASE WHEN fuze_time_played_delta > 0 THEN fuze_lost_delta END) AS fuze_lost_delta,
SUM(fuze_lost) AS fuze_lost,
AVG(glaz_time_played_delta) AS glaz_time_played_delta,
SUM(glaz_time_played) AS glaz_time_played,
AVG(CASE WHEN glaz_time_played_delta > 0 THEN glaz_kills_delta END) AS glaz_kills_delta,
SUM(glaz_kills) AS glaz_kills,
AVG(CASE WHEN glaz_time_played_delta > 0 THEN glaz_deaths_delta END) AS glaz_deaths_delta,
SUM(glaz_deaths) AS glaz_deaths,
AVG(CASE WHEN glaz_time_played_delta > 0 THEN glaz_won_delta END) AS glaz_won_delta,
SUM(glaz_won) AS glaz_won,
AVG(CASE WHEN glaz_time_played_delta > 0 THEN glaz_lost_delta END) AS glaz_lost_delta,
SUM(glaz_lost) AS glaz_lost,
AVG(mira_time_played_delta) AS mira_time_played_delta,
SUM(mira_time_played) AS mira_time_played,
AVG(CASE WHEN mira_time_played_delta > 0 THEN mira_kills_delta END) AS mira_kills_delta,
SUM(mira_kills) AS mira_kills,
AVG(CASE WHEN mira_time_played_delta > 0 THEN mira_deaths_delta END) AS mira_deaths_delta,
SUM(mira_deaths) AS mira_deaths,
AVG(CASE WHEN mira_time_played_delta > 0 THEN mira_won_delta END) AS mira_won_delta,
SUM(mira_won) AS mira_won,
AVG(CASE WHEN mira_time_played_delta > 0 THEN mira_lost_delta END) AS mira_lost_delta,
SUM(mira_lost) AS mira_lost,
AVG(mute_time_played_delta) AS mute_time_played_delta,
SUM(mute_time_played) AS mute_time_played,
AVG(CASE WHEN mute_time_played_delta > 0 THEN mute_kills_delta END) AS mute_kills_delta,
SUM(mute_kills) AS mute_kills,
AVG(CASE WHEN mute_time_played_delta > 0 THEN mute_deaths_delta END) AS mute_deaths_delta,
SUM(mute_deaths) AS mute_deaths,
AVG(CASE WHEN mute_time_played_delta > 0 THEN mute_won_delta END) AS mute_won_delta,
SUM(mute_won) AS mute_won,
AVG(CASE WHEN mute_time_played_delta > 0 THEN mute_lost_delta END) AS mute_lost_delta,
SUM(mute_lost) AS mute_lost,
AVG(rook_time_played_delta) AS rook_time_played_delta,
SUM(rook_time_played) AS rook_time_played,
AVG(CASE WHEN rook_time_played_delta > 0 THEN rook_kills_delta END) AS rook_kills_delta,
SUM(rook_kills) AS rook_kills,
AVG(CASE WHEN rook_time_played_delta > 0 THEN rook_deaths_delta END) AS rook_deaths_delta,
SUM(rook_deaths) AS rook_deaths,
AVG(CASE WHEN rook_time_played_delta > 0 THEN rook_won_delta END) AS rook_won_delta,
SUM(rook_won) AS rook_won,
AVG(CASE WHEN rook_time_played_delta > 0 THEN rook_lost_delta END) AS rook_lost_delta,
SUM(rook_lost) AS rook_lost,
AVG(blitz_time_played_delta) AS blitz_time_played_delta,
SUM(blitz_time_played) AS blitz_time_played,
AVG(CASE WHEN blitz_time_played_delta > 0 THEN blitz_kills_delta END) AS blitz_kills_delta,
SUM(blitz_kills) AS blitz_kills,
AVG(CASE WHEN blitz_time_played_delta > 0 THEN blitz_deaths_delta END) AS blitz_deaths_delta,
SUM(blitz_deaths) AS blitz_deaths,
AVG(CASE WHEN blitz_time_played_delta > 0 THEN blitz_won_delta END) AS blitz_won_delta,
SUM(blitz_won) AS blitz_won,
AVG(CASE WHEN blitz_time_played_delta > 0 THEN blitz_lost_delta END) AS blitz_lost_delta,
SUM(blitz_lost) AS blitz_lost,
AVG(frost_time_played_delta) AS frost_time_played_delta,
SUM(frost_time_played) AS frost_time_played,
AVG(CASE WHEN frost_time_played_delta > 0 THEN frost_kills_delta END) AS frost_kills_delta,
SUM(frost_kills) AS frost_kills,
AVG(CASE WHEN frost_time_played_delta > 0 THEN frost_deaths_delta END) AS frost_deaths_delta,
SUM(frost_deaths) AS frost_deaths,
AVG(CASE WHEN frost_time_played_delta > 0 THEN frost_won_delta END) AS frost_won_delta,
SUM(frost_won) AS frost_won,
AVG(CASE WHEN frost_time_played_delta > 0 THEN frost_lost_delta END) AS frost_lost_delta,
SUM(frost_lost) AS frost_lost,
AVG(jager_time_played_delta) AS jager_time_played_delta,
SUM(jager_time_played) AS jager_time_played,
AVG(CASE WHEN jager_time_played_delta > 0 THEN jager_kills_delta END) AS jager_kills_delta,
SUM(jager_kills) AS jager_kills,
AVG(CASE WHEN jager_time_played_delta > 0 THEN jager_deaths_delta END) AS jager_deaths_delta,
SUM(jager_deaths) AS jager_deaths,
AVG(CASE WHEN jager_time_played_delta > 0 THEN jager_won_delta END) AS jager_won_delta,
SUM(jager_won) AS jager_won,
AVG(CASE WHEN jager_time_played_delta > 0 THEN jager_lost_delta END) AS jager_lost_delta,
SUM(jager_lost) AS jager_lost,
AVG(pulse_time_played_delta) AS pulse_time_played_delta,
SUM(pulse_time_played) AS pulse_time_played,
AVG(CASE WHEN pulse_time_played_delta > 0 THEN pulse_kills_delta END) AS pulse_kills_delta,
SUM(pulse_kills) AS pulse_kills,
AVG(CASE WHEN pulse_time_played_delta > 0 THEN pulse_deaths_delta END) AS pulse_deaths_delta,
SUM(pulse_deaths) AS pulse_deaths,
AVG(CASE WHEN pulse_time_played_delta > 0 THEN pulse_won_delta END) AS pulse_won_delta,
SUM(pulse_won) AS pulse_won,
AVG(CASE WHEN pulse_time_played_delta > 0 THEN pulse_lost_delta END) AS pulse_lost_delta,
SUM(pulse_lost) AS pulse_lost,
AVG(smoke_time_played_delta) AS smoke_time_played_delta,
SUM(smoke_time_played) AS smoke_time_played,
AVG(CASE WHEN smoke_time_played_delta > 0 THEN smoke_kills_delta END) AS smoke_kills_delta,
SUM(smoke_kills) AS smoke_kills,
AVG(CASE WHEN smoke_time_played_delta > 0 THEN smoke_deaths_delta END) AS smoke_deaths_delta,
SUM(smoke_deaths) AS smoke_deaths,
AVG(CASE WHEN smoke_time_played_delta > 0 THEN smoke_won_delta END) AS smoke_won_delta,
SUM(smoke_won) AS smoke_won,
AVG(CASE WHEN smoke_time_played_delta > 0 THEN smoke_lost_delta END) AS smoke_lost_delta,
SUM(smoke_lost) AS smoke_lost,
AVG(bandit_time_played_delta) AS bandit_time_played_delta,
SUM(bandit_time_played) AS bandit_time_played,
AVG(CASE WHEN bandit_time_played_delta > 0 THEN bandit_kills_delta END) AS bandit_kills_delta,
SUM(bandit_kills) AS bandit_kills,
AVG(CASE WHEN bandit_time_played_delta > 0 THEN bandit_deaths_delta END) AS bandit_deaths_delta,
SUM(bandit_deaths) AS bandit_deaths,
AVG(CASE WHEN bandit_time_played_delta > 0 THEN bandit_won_delta END) AS bandit_won_delta,
SUM(bandit_won) AS bandit_won,
AVG(CASE WHEN bandit_time_played_delta > 0 THEN bandit_lost_delta END) AS bandit_lost_delta,
SUM(bandit_lost) AS bandit_lost,
AVG(castle_time_played_delta) AS castle_time_played_delta,
SUM(castle_time_played) AS castle_time_played,
AVG(CASE WHEN castle_time_played_delta > 0 THEN castle_kills_delta END) AS castle_kills_delta,
SUM(castle_kills) AS castle_kills,
AVG(CASE WHEN castle_time_played_delta > 0 THEN castle_deaths_delta END) AS castle_deaths_delta,
SUM(castle_deaths) AS castle_deaths,
AVG(CASE WHEN castle_time_played_delta > 0 THEN castle_won_delta END) AS castle_won_delta,
SUM(castle_won) AS castle_won,
AVG(CASE WHEN castle_time_played_delta > 0 THEN castle_lost_delta END) AS castle_lost_delta,
SUM(castle_lost) AS castle_lost,
AVG(hibana_time_played_delta) AS hibana_time_played_delta,
SUM(hibana_time_played) AS hibana_time_played,
AVG(CASE WHEN hibana_time_played_delta > 0 THEN hibana_kills_delta END) AS hibana_kills_delta,
SUM(hibana_kills) AS hibana_kills,
AVG(CASE WHEN hibana_time_played_delta > 0 THEN hibana_deaths_delta END) AS hibana_deaths_delta,
SUM(hibana_deaths) AS hibana_deaths,
AVG(CASE WHEN hibana_time_played_delta > 0 THEN hibana_won_delta END) AS hibana_won_delta,
SUM(hibana_won) AS hibana_won,
AVG(CASE WHEN hibana_time_played_delta > 0 THEN hibana_lost_delta END) AS hibana_lost_delta,
SUM(hibana_lost) AS hibana_lost,
AVG(jackal_time_played_delta) AS jackal_time_played_delta,
SUM(jackal_time_played) AS jackal_time_played,
AVG(CASE WHEN jackal_time_played_delta > 0 THEN jackal_kills_delta END) AS jackal_kills_delta,
SUM(jackal_kills) AS jackal_kills,
AVG(CASE WHEN jackal_time_played_delta > 0 THEN jackal_deaths_delta END) AS jackal_deaths_delta,
SUM(jackal_deaths) AS jackal_deaths,
AVG(CASE WHEN jackal_time_played_delta > 0 THEN jackal_won_delta END) AS jackal_won_delta,
SUM(jackal_won) AS jackal_won,
AVG(CASE WHEN jackal_time_played_delta > 0 THEN jackal_lost_delta END) AS jackal_lost_delta,
SUM(jackal_lost) AS jackal_lost,
AVG(kapkan_time_played_delta) AS kapkan_time_played_delta,
SUM(kapkan_time_played) AS kapkan_time_played,
AVG(CASE WHEN kapkan_time_played_delta > 0 THEN kapkan_kills_delta END) AS kapkan_kills_delta,
SUM(kapkan_kills) AS kapkan_kills,
AVG(CASE WHEN kapkan_time_played_delta > 0 THEN kapkan_deaths_delta END) AS kapkan_deaths_delta,
SUM(kapkan_deaths) AS kapkan_deaths,
AVG(CASE WHEN kapkan_time_played_delta > 0 THEN kapkan_won_delta END) AS kapkan_won_delta,
SUM(kapkan_won) AS kapkan_won,
AVG(CASE WHEN kapkan_time_played_delta > 0 THEN kapkan_lost_delta END) AS kapkan_lost_delta,
SUM(kapkan_lost) AS kapkan_lost,
AVG(sledge_time_played_delta) AS sledge_time_played_delta,
SUM(sledge_time_played) AS sledge_time_played,
AVG(CASE WHEN sledge_time_played_delta > 0 THEN sledge_kills_delta END) AS sledge_kills_delta,
SUM(sledge_kills) AS sledge_kills,
AVG(CASE WHEN sledge_time_played_delta > 0 THEN sledge_deaths_delta END) AS sledge_deaths_delta,
SUM(sledge_deaths) AS sledge_deaths,
AVG(CASE WHEN sledge_time_played_delta > 0 THEN sledge_won_delta END) AS sledge_won_delta,
SUM(sledge_won) AS sledge_won,
AVG(CASE WHEN sledge_time_played_delta > 0 THEN sledge_lost_delta END) AS sledge_lost_delta,
SUM(sledge_lost) AS sledge_lost,
AVG(twitch_time_played_delta) AS twitch_time_played_delta,
SUM(twitch_time_played) AS twitch_time_played,
AVG(CASE WHEN twitch_time_played_delta > 0 THEN twitch_kills_delta END) AS twitch_kills_delta,
SUM(twitch_kills) AS twitch_kills,
AVG(CASE WHEN twitch_time_played_delta > 0 THEN twitch_deaths_delta END) AS twitch_deaths_delta,
SUM(twitch_deaths) AS twitch_deaths,
AVG(CASE WHEN twitch_time_played_delta > 0 THEN twitch_won_delta END) AS twitch_won_delta,
SUM(twitch_won) AS twitch_won,
AVG(CASE WHEN twitch_time_played_delta > 0 THEN twitch_lost_delta END) AS twitch_lost_delta,
SUM(twitch_lost) AS twitch_lost,
AVG(capitao_time_played_delta) AS capitao_time_played_delta,
SUM(capitao_time_played) AS capitao_time_played,
AVG(CASE WHEN capitao_time_played_delta > 0 THEN capitao_kills_delta END) AS capitao_kills_delta,
SUM(capitao_kills) AS capitao_kills,
AVG(CASE WHEN capitao_time_played_delta > 0 THEN capitao_deaths_delta END) AS capitao_deaths_delta,
SUM(capitao_deaths) AS capitao_deaths,
AVG(CASE WHEN capitao_time_played_delta > 0 THEN capitao_won_delta END) AS capitao_won_delta,
SUM(capitao_won) AS capitao_won,
AVG(CASE WHEN capitao_time_played_delta > 0 THEN capitao_lost_delta END) AS capitao_lost_delta,
SUM(capitao_lost) AS capitao_lost,
AVG(caveira_time_played_delta) AS caveira_time_played_delta,
SUM(caveira_time_played) AS caveira_time_played,
AVG(CASE WHEN caveira_time_played_delta > 0 THEN caveira_kills_delta END) AS caveira_kills_delta,
SUM(caveira_kills) AS caveira_kills,
AVG(CASE WHEN caveira_time_played_delta > 0 THEN caveira_deaths_delta END) AS caveira_deaths_delta,
SUM(caveira_deaths) AS caveira_deaths,
AVG(CASE WHEN caveira_time_played_delta > 0 THEN caveira_won_delta END) AS caveira_won_delta,
SUM(caveira_won) AS caveira_won,
AVG(CASE WHEN caveira_time_played_delta > 0 THEN caveira_lost_delta END) AS caveira_lost_delta,
SUM(caveira_lost) AS caveira_lost,
AVG(montagne_time_played_delta) AS montagne_time_played_delta,
SUM(montagne_time_played) AS montagne_time_played,
AVG(CASE WHEN montagne_time_played_delta > 0 THEN montagne_kills_delta END) AS montagne_kills_delta,
SUM(montagne_kills) AS montagne_kills,
AVG(CASE WHEN montagne_time_played_delta > 0 THEN montagne_deaths_delta END) AS montagne_deaths_delta,
SUM(montagne_deaths) AS montagne_deaths,
AVG(CASE WHEN montagne_time_played_delta > 0 THEN montagne_won_delta END) AS montagne_won_delta,
SUM(montagne_won) AS montagne_won,
AVG(CASE WHEN montagne_time_played_delta > 0 THEN montagne_lost_delta END) AS montagne_lost_delta,
SUM(montagne_lost) AS montagne_lost,
AVG(tachanka_time_played_delta) AS tachanka_time_played_delta,
SUM(tachanka_time_played) AS tachanka_time_played,
AVG(CASE WHEN tachanka_time_played_delta > 0 THEN tachanka_kills_delta END) AS tachanka_kills_delta,
SUM(tachanka_kills) AS tachanka_kills,
AVG(CASE WHEN tachanka_time_played_delta > 0 THEN tachanka_deaths_delta END) AS tachanka_deaths_delta,
SUM(tachanka_deaths) AS tachanka_deaths,
AVG(CASE WHEN tachanka_time_played_delta > 0 THEN tachanka_won_delta END) AS tachanka_won_delta,
SUM(tachanka_won) AS tachanka_won,
AVG(CASE WHEN tachanka_time_played_delta > 0 THEN tachanka_lost_delta END) AS tachanka_lost_delta,
SUM(tachanka_lost) AS tachanka_lost,
AVG(thatcher_time_played_delta) AS thatcher_time_played_delta,
SUM(thatcher_time_played) AS thatcher_time_played,
AVG(CASE WHEN thatcher_time_played_delta > 0 THEN thatcher_kills_delta END) AS thatcher_kills_delta,
SUM(thatcher_kills) AS thatcher_kills,
AVG(CASE WHEN thatcher_time_played_delta > 0 THEN thatcher_deaths_delta END) AS thatcher_deaths_delta,
SUM(thatcher_deaths) AS thatcher_deaths,
AVG(CASE WHEN thatcher_time_played_delta > 0 THEN thatcher_won_delta END) AS thatcher_won_delta,
SUM(thatcher_won) AS thatcher_won,
AVG(CASE WHEN thatcher_time_played_delta > 0 THEN thatcher_lost_delta END) AS thatcher_lost_delta,
SUM(thatcher_lost) AS thatcher_lost,
AVG(thermite_time_played_delta) AS thermite_time_played_delta,
SUM(thermite_time_played) AS thermite_time_played,
AVG(CASE WHEN thermite_time_played_delta > 0 THEN thermite_kills_delta END) AS thermite_kills_delta,
SUM(thermite_kills) AS thermite_kills,
AVG(CASE WHEN thermite_time_played_delta > 0 THEN thermite_deaths_delta END) AS thermite_deaths_delta,
SUM(thermite_deaths) AS thermite_deaths,
AVG(CASE WHEN thermite_time_played_delta > 0 THEN thermite_won_delta END) AS thermite_won_delta,
SUM(thermite_won) AS thermite_won,
AVG(CASE WHEN thermite_time_played_delta > 0 THEN thermite_lost_delta END) AS thermite_lost_delta,
SUM(thermite_lost) AS thermite_lost,
AVG(valkyrie_time_played_delta) AS valkyrie_time_played_delta,
SUM(valkyrie_time_played) AS valkyrie_time_played,
AVG(CASE WHEN valkyrie_time_played_delta > 0 THEN valkyrie_kills_delta END) AS valkyrie_kills_delta,
SUM(valkyrie_kills) AS valkyrie_kills,
AVG(CASE WHEN valkyrie_time_played_delta > 0 THEN valkyrie_deaths_delta END) AS valkyrie_deaths_delta,
SUM(valkyrie_deaths) AS valkyrie_deaths,
AVG(CASE WHEN valkyrie_time_played_delta > 0 THEN valkyrie_won_delta END) AS valkyrie_won_delta,
SUM(valkyrie_won) AS valkyrie_won,
AVG(CASE WHEN valkyrie_time_played_delta > 0 THEN valkyrie_lost_delta END) AS valkyrie_lost_delta,
SUM(valkyrie_lost) AS valkyrie_lost,
AVG(blackbeard_time_played_delta) AS blackbeard_time_played_delta,
SUM(blackbeard_time_played) AS blackbeard_time_played,
AVG(CASE WHEN blackbeard_time_played_delta > 0 THEN blackbeard_kills_delta END) AS blackbeard_kills_delta,
SUM(blackbeard_kills) AS blackbeard_kills,
AVG(CASE WHEN blackbeard_time_played_delta > 0 THEN blackbeard_deaths_delta END) AS blackbeard_deaths_delta,
SUM(blackbeard_deaths) AS blackbeard_deaths,
AVG(CASE WHEN blackbeard_time_played_delta > 0 THEN blackbeard_won_delta END) AS blackbeard_won_delta,
SUM(blackbeard_won) AS blackbeard_won,
AVG(CASE WHEN blackbeard_time_played_delta > 0 THEN blackbeard_lost_delta END) AS blackbeard_lost_delta,
SUM(blackbeard_lost) AS blackbeard_lost
FROM (
SELECT
(p2.operators->'iq'->>'timePlayed')::numeric AS iq_time_played,
(p2.operators->'iq'->>'timePlayed')::numeric - (p1.operators->'iq'->>'timePlayed')::numeric AS iq_time_played_delta,
(p2.operators->'iq'->>'kills')::numeric AS iq_kills,
(p2.operators->'iq'->>'kills')::numeric - (p1.operators->'iq'->>'kills')::numeric AS iq_kills_delta,
(p2.operators->'iq'->>'deaths')::numeric AS iq_deaths,
(p2.operators->'iq'->>'deaths')::numeric - (p1.operators->'iq'->>'deaths')::numeric AS iq_deaths_delta,
(p2.operators->'iq'->>'won')::numeric AS iq_won,
(p2.operators->'iq'->>'won')::numeric - (p1.operators->'iq'->>'won')::numeric AS iq_won_delta,
(p2.operators->'iq'->>'lost')::numeric AS iq_lost,
(p2.operators->'iq'->>'lost')::numeric - (p1.operators->'iq'->>'lost')::numeric AS iq_lost_delta,
(p2.operators->'ash'->>'timePlayed')::numeric AS ash_time_played,
(p2.operators->'ash'->>'timePlayed')::numeric - (p1.operators->'ash'->>'timePlayed')::numeric AS ash_time_played_delta,
(p2.operators->'ash'->>'kills')::numeric AS ash_kills,
(p2.operators->'ash'->>'kills')::numeric - (p1.operators->'ash'->>'kills')::numeric AS ash_kills_delta,
(p2.operators->'ash'->>'deaths')::numeric AS ash_deaths,
(p2.operators->'ash'->>'deaths')::numeric - (p1.operators->'ash'->>'deaths')::numeric AS ash_deaths_delta,
(p2.operators->'ash'->>'won')::numeric AS ash_won,
(p2.operators->'ash'->>'won')::numeric - (p1.operators->'ash'->>'won')::numeric AS ash_won_delta,
(p2.operators->'ash'->>'lost')::numeric AS ash_lost,
(p2.operators->'ash'->>'lost')::numeric - (p1.operators->'ash'->>'lost')::numeric AS ash_lost_delta,
(p2.operators->'doc'->>'timePlayed')::numeric AS doc_time_played,
(p2.operators->'doc'->>'timePlayed')::numeric - (p1.operators->'doc'->>'timePlayed')::numeric AS doc_time_played_delta,
(p2.operators->'doc'->>'kills')::numeric AS doc_kills,
(p2.operators->'doc'->>'kills')::numeric - (p1.operators->'doc'->>'kills')::numeric AS doc_kills_delta,
(p2.operators->'doc'->>'deaths')::numeric AS doc_deaths,
(p2.operators->'doc'->>'deaths')::numeric - (p1.operators->'doc'->>'deaths')::numeric AS doc_deaths_delta,
(p2.operators->'doc'->>'won')::numeric AS doc_won,
(p2.operators->'doc'->>'won')::numeric - (p1.operators->'doc'->>'won')::numeric AS doc_won_delta,
(p2.operators->'doc'->>'lost')::numeric AS doc_lost,
(p2.operators->'doc'->>'lost')::numeric - (p1.operators->'doc'->>'lost')::numeric AS doc_lost_delta,
(p2.operators->'buck'->>'timePlayed')::numeric AS buck_time_played,
(p2.operators->'buck'->>'timePlayed')::numeric - (p1.operators->'buck'->>'timePlayed')::numeric AS buck_time_played_delta,
(p2.operators->'buck'->>'kills')::numeric AS buck_kills,
(p2.operators->'buck'->>'kills')::numeric - (p1.operators->'buck'->>'kills')::numeric AS buck_kills_delta,
(p2.operators->'buck'->>'deaths')::numeric AS buck_deaths,
(p2.operators->'buck'->>'deaths')::numeric - (p1.operators->'buck'->>'deaths')::numeric AS buck_deaths_delta,
(p2.operators->'buck'->>'won')::numeric AS buck_won,
(p2.operators->'buck'->>'won')::numeric - (p1.operators->'buck'->>'won')::numeric AS buck_won_delta,
(p2.operators->'buck'->>'lost')::numeric AS buck_lost,
(p2.operators->'buck'->>'lost')::numeric - (p1.operators->'buck'->>'lost')::numeric AS buck_lost_delta,
(p2.operators->'echo'->>'timePlayed')::numeric AS echo_time_played,
(p2.operators->'echo'->>'timePlayed')::numeric - (p1.operators->'echo'->>'timePlayed')::numeric AS echo_time_played_delta,
(p2.operators->'echo'->>'kills')::numeric AS echo_kills,
(p2.operators->'echo'->>'kills')::numeric - (p1.operators->'echo'->>'kills')::numeric AS echo_kills_delta,
(p2.operators->'echo'->>'deaths')::numeric AS echo_deaths,
(p2.operators->'echo'->>'deaths')::numeric - (p1.operators->'echo'->>'deaths')::numeric AS echo_deaths_delta,
(p2.operators->'echo'->>'won')::numeric AS echo_won,
(p2.operators->'echo'->>'won')::numeric - (p1.operators->'echo'->>'won')::numeric AS echo_won_delta,
(p2.operators->'echo'->>'lost')::numeric AS echo_lost,
(p2.operators->'echo'->>'lost')::numeric - (p1.operators->'echo'->>'lost')::numeric AS echo_lost_delta,
(p2.operators->'fuze'->>'timePlayed')::numeric AS fuze_time_played,
(p2.operators->'fuze'->>'timePlayed')::numeric - (p1.operators->'fuze'->>'timePlayed')::numeric AS fuze_time_played_delta,
(p2.operators->'fuze'->>'kills')::numeric AS fuze_kills,
(p2.operators->'fuze'->>'kills')::numeric - (p1.operators->'fuze'->>'kills')::numeric AS fuze_kills_delta,
(p2.operators->'fuze'->>'deaths')::numeric AS fuze_deaths,
(p2.operators->'fuze'->>'deaths')::numeric - (p1.operators->'fuze'->>'deaths')::numeric AS fuze_deaths_delta,
(p2.operators->'fuze'->>'won')::numeric AS fuze_won,
(p2.operators->'fuze'->>'won')::numeric - (p1.operators->'fuze'->>'won')::numeric AS fuze_won_delta,
(p2.operators->'fuze'->>'lost')::numeric AS fuze_lost,
(p2.operators->'fuze'->>'lost')::numeric - (p1.operators->'fuze'->>'lost')::numeric AS fuze_lost_delta,
(p2.operators->'glaz'->>'timePlayed')::numeric AS glaz_time_played,
(p2.operators->'glaz'->>'timePlayed')::numeric - (p1.operators->'glaz'->>'timePlayed')::numeric AS glaz_time_played_delta,
(p2.operators->'glaz'->>'kills')::numeric AS glaz_kills,
(p2.operators->'glaz'->>'kills')::numeric - (p1.operators->'glaz'->>'kills')::numeric AS glaz_kills_delta,
(p2.operators->'glaz'->>'deaths')::numeric AS glaz_deaths,
(p2.operators->'glaz'->>'deaths')::numeric - (p1.operators->'glaz'->>'deaths')::numeric AS glaz_deaths_delta,
(p2.operators->'glaz'->>'won')::numeric AS glaz_won,
(p2.operators->'glaz'->>'won')::numeric - (p1.operators->'glaz'->>'won')::numeric AS glaz_won_delta,
(p2.operators->'glaz'->>'lost')::numeric AS glaz_lost,
(p2.operators->'glaz'->>'lost')::numeric - (p1.operators->'glaz'->>'lost')::numeric AS glaz_lost_delta,
(p2.operators->'mira'->>'timePlayed')::numeric AS mira_time_played,
(p2.operators->'mira'->>'timePlayed')::numeric - (p1.operators->'mira'->>'timePlayed')::numeric AS mira_time_played_delta,
(p2.operators->'mira'->>'kills')::numeric AS mira_kills,
(p2.operators->'mira'->>'kills')::numeric - (p1.operators->'mira'->>'kills')::numeric AS mira_kills_delta,
(p2.operators->'mira'->>'deaths')::numeric AS mira_deaths,
(p2.operators->'mira'->>'deaths')::numeric - (p1.operators->'mira'->>'deaths')::numeric AS mira_deaths_delta,
(p2.operators->'mira'->>'won')::numeric AS mira_won,
(p2.operators->'mira'->>'won')::numeric - (p1.operators->'mira'->>'won')::numeric AS mira_won_delta,
(p2.operators->'mira'->>'lost')::numeric AS mira_lost,
(p2.operators->'mira'->>'lost')::numeric - (p1.operators->'mira'->>'lost')::numeric AS mira_lost_delta,
(p2.operators->'mute'->>'timePlayed')::numeric AS mute_time_played,
(p2.operators->'mute'->>'timePlayed')::numeric - (p1.operators->'mute'->>'timePlayed')::numeric AS mute_time_played_delta,
(p2.operators->'mute'->>'kills')::numeric AS mute_kills,
(p2.operators->'mute'->>'kills')::numeric - (p1.operators->'mute'->>'kills')::numeric AS mute_kills_delta,
(p2.operators->'mute'->>'deaths')::numeric AS mute_deaths,
(p2.operators->'mute'->>'deaths')::numeric - (p1.operators->'mute'->>'deaths')::numeric AS mute_deaths_delta,
(p2.operators->'mute'->>'won')::numeric AS mute_won,
(p2.operators->'mute'->>'won')::numeric - (p1.operators->'mute'->>'won')::numeric AS mute_won_delta,
(p2.operators->'mute'->>'lost')::numeric AS mute_lost,
(p2.operators->'mute'->>'lost')::numeric - (p1.operators->'mute'->>'lost')::numeric AS mute_lost_delta,
(p2.operators->'rook'->>'timePlayed')::numeric AS rook_time_played,
(p2.operators->'rook'->>'timePlayed')::numeric - (p1.operators->'rook'->>'timePlayed')::numeric AS rook_time_played_delta,
(p2.operators->'rook'->>'kills')::numeric AS rook_kills,
(p2.operators->'rook'->>'kills')::numeric - (p1.operators->'rook'->>'kills')::numeric AS rook_kills_delta,
(p2.operators->'rook'->>'deaths')::numeric AS rook_deaths,
(p2.operators->'rook'->>'deaths')::numeric - (p1.operators->'rook'->>'deaths')::numeric AS rook_deaths_delta,
(p2.operators->'rook'->>'won')::numeric AS rook_won,
(p2.operators->'rook'->>'won')::numeric - (p1.operators->'rook'->>'won')::numeric AS rook_won_delta,
(p2.operators->'rook'->>'lost')::numeric AS rook_lost,
(p2.operators->'rook'->>'lost')::numeric - (p1.operators->'rook'->>'lost')::numeric AS rook_lost_delta,
(p2.operators->'blitz'->>'timePlayed')::numeric AS blitz_time_played,
(p2.operators->'blitz'->>'timePlayed')::numeric - (p1.operators->'blitz'->>'timePlayed')::numeric AS blitz_time_played_delta,
(p2.operators->'blitz'->>'kills')::numeric AS blitz_kills,
(p2.operators->'blitz'->>'kills')::numeric - (p1.operators->'blitz'->>'kills')::numeric AS blitz_kills_delta,
(p2.operators->'blitz'->>'deaths')::numeric AS blitz_deaths,
(p2.operators->'blitz'->>'deaths')::numeric - (p1.operators->'blitz'->>'deaths')::numeric AS blitz_deaths_delta,
(p2.operators->'blitz'->>'won')::numeric AS blitz_won,
(p2.operators->'blitz'->>'won')::numeric - (p1.operators->'blitz'->>'won')::numeric AS blitz_won_delta,
(p2.operators->'blitz'->>'lost')::numeric AS blitz_lost,
(p2.operators->'blitz'->>'lost')::numeric - (p1.operators->'blitz'->>'lost')::numeric AS blitz_lost_delta,
(p2.operators->'frost'->>'timePlayed')::numeric AS frost_time_played,
(p2.operators->'frost'->>'timePlayed')::numeric - (p1.operators->'frost'->>'timePlayed')::numeric AS frost_time_played_delta,
(p2.operators->'frost'->>'kills')::numeric AS frost_kills,
(p2.operators->'frost'->>'kills')::numeric - (p1.operators->'frost'->>'kills')::numeric AS frost_kills_delta,
(p2.operators->'frost'->>'deaths')::numeric AS frost_deaths,
(p2.operators->'frost'->>'deaths')::numeric - (p1.operators->'frost'->>'deaths')::numeric AS frost_deaths_delta,
(p2.operators->'frost'->>'won')::numeric AS frost_won,
(p2.operators->'frost'->>'won')::numeric - (p1.operators->'frost'->>'won')::numeric AS frost_won_delta,
(p2.operators->'frost'->>'lost')::numeric AS frost_lost,
(p2.operators->'frost'->>'lost')::numeric - (p1.operators->'frost'->>'lost')::numeric AS frost_lost_delta,
(p2.operators->'jager'->>'timePlayed')::numeric AS jager_time_played,
(p2.operators->'jager'->>'timePlayed')::numeric - (p1.operators->'jager'->>'timePlayed')::numeric AS jager_time_played_delta,
(p2.operators->'jager'->>'kills')::numeric AS jager_kills,
(p2.operators->'jager'->>'kills')::numeric - (p1.operators->'jager'->>'kills')::numeric AS jager_kills_delta,
(p2.operators->'jager'->>'deaths')::numeric AS jager_deaths,
(p2.operators->'jager'->>'deaths')::numeric - (p1.operators->'jager'->>'deaths')::numeric AS jager_deaths_delta,
(p2.operators->'jager'->>'won')::numeric AS jager_won,
(p2.operators->'jager'->>'won')::numeric - (p1.operators->'jager'->>'won')::numeric AS jager_won_delta,
(p2.operators->'jager'->>'lost')::numeric AS jager_lost,
(p2.operators->'jager'->>'lost')::numeric - (p1.operators->'jager'->>'lost')::numeric AS jager_lost_delta,
(p2.operators->'pulse'->>'timePlayed')::numeric AS pulse_time_played,
(p2.operators->'pulse'->>'timePlayed')::numeric - (p1.operators->'pulse'->>'timePlayed')::numeric AS pulse_time_played_delta,
(p2.operators->'pulse'->>'kills')::numeric AS pulse_kills,
(p2.operators->'pulse'->>'kills')::numeric - (p1.operators->'pulse'->>'kills')::numeric AS pulse_kills_delta,
(p2.operators->'pulse'->>'deaths')::numeric AS pulse_deaths,
(p2.operators->'pulse'->>'deaths')::numeric - (p1.operators->'pulse'->>'deaths')::numeric AS pulse_deaths_delta,
(p2.operators->'pulse'->>'won')::numeric AS pulse_won,
(p2.operators->'pulse'->>'won')::numeric - (p1.operators->'pulse'->>'won')::numeric AS pulse_won_delta,
(p2.operators->'pulse'->>'lost')::numeric AS pulse_lost,
(p2.operators->'pulse'->>'lost')::numeric - (p1.operators->'pulse'->>'lost')::numeric AS pulse_lost_delta,
(p2.operators->'smoke'->>'timePlayed')::numeric AS smoke_time_played,
(p2.operators->'smoke'->>'timePlayed')::numeric - (p1.operators->'smoke'->>'timePlayed')::numeric AS smoke_time_played_delta,
(p2.operators->'smoke'->>'kills')::numeric AS smoke_kills,
(p2.operators->'smoke'->>'kills')::numeric - (p1.operators->'smoke'->>'kills')::numeric AS smoke_kills_delta,
(p2.operators->'smoke'->>'deaths')::numeric AS smoke_deaths,
(p2.operators->'smoke'->>'deaths')::numeric - (p1.operators->'smoke'->>'deaths')::numeric AS smoke_deaths_delta,
(p2.operators->'smoke'->>'won')::numeric AS smoke_won,
(p2.operators->'smoke'->>'won')::numeric - (p1.operators->'smoke'->>'won')::numeric AS smoke_won_delta,
(p2.operators->'smoke'->>'lost')::numeric AS smoke_lost,
(p2.operators->'smoke'->>'lost')::numeric - (p1.operators->'smoke'->>'lost')::numeric AS smoke_lost_delta,
(p2.operators->'bandit'->>'timePlayed')::numeric AS bandit_time_played,
(p2.operators->'bandit'->>'timePlayed')::numeric - (p1.operators->'bandit'->>'timePlayed')::numeric AS bandit_time_played_delta,
(p2.operators->'bandit'->>'kills')::numeric AS bandit_kills,
(p2.operators->'bandit'->>'kills')::numeric - (p1.operators->'bandit'->>'kills')::numeric AS bandit_kills_delta,
(p2.operators->'bandit'->>'deaths')::numeric AS bandit_deaths,
(p2.operators->'bandit'->>'deaths')::numeric - (p1.operators->'bandit'->>'deaths')::numeric AS bandit_deaths_delta,
(p2.operators->'bandit'->>'won')::numeric AS bandit_won,
(p2.operators->'bandit'->>'won')::numeric - (p1.operators->'bandit'->>'won')::numeric AS bandit_won_delta,
(p2.operators->'bandit'->>'lost')::numeric AS bandit_lost,
(p2.operators->'bandit'->>'lost')::numeric - (p1.operators->'bandit'->>'lost')::numeric AS bandit_lost_delta,
(p2.operators->'castle'->>'timePlayed')::numeric AS castle_time_played,
(p2.operators->'castle'->>'timePlayed')::numeric - (p1.operators->'castle'->>'timePlayed')::numeric AS castle_time_played_delta,
(p2.operators->'castle'->>'kills')::numeric AS castle_kills,
(p2.operators->'castle'->>'kills')::numeric - (p1.operators->'castle'->>'kills')::numeric AS castle_kills_delta,
(p2.operators->'castle'->>'deaths')::numeric AS castle_deaths,
(p2.operators->'castle'->>'deaths')::numeric - (p1.operators->'castle'->>'deaths')::numeric AS castle_deaths_delta,
(p2.operators->'castle'->>'won')::numeric AS castle_won,
(p2.operators->'castle'->>'won')::numeric - (p1.operators->'castle'->>'won')::numeric AS castle_won_delta,
(p2.operators->'castle'->>'lost')::numeric AS castle_lost,
(p2.operators->'castle'->>'lost')::numeric - (p1.operators->'castle'->>'lost')::numeric AS castle_lost_delta,
(p2.operators->'hibana'->>'timePlayed')::numeric AS hibana_time_played,
(p2.operators->'hibana'->>'timePlayed')::numeric - (p1.operators->'hibana'->>'timePlayed')::numeric AS hibana_time_played_delta,
(p2.operators->'hibana'->>'kills')::numeric AS hibana_kills,
(p2.operators->'hibana'->>'kills')::numeric - (p1.operators->'hibana'->>'kills')::numeric AS hibana_kills_delta,
(p2.operators->'hibana'->>'deaths')::numeric AS hibana_deaths,
(p2.operators->'hibana'->>'deaths')::numeric - (p1.operators->'hibana'->>'deaths')::numeric AS hibana_deaths_delta,
(p2.operators->'hibana'->>'won')::numeric AS hibana_won,
(p2.operators->'hibana'->>'won')::numeric - (p1.operators->'hibana'->>'won')::numeric AS hibana_won_delta,
(p2.operators->'hibana'->>'lost')::numeric AS hibana_lost,
(p2.operators->'hibana'->>'lost')::numeric - (p1.operators->'hibana'->>'lost')::numeric AS hibana_lost_delta,
(p2.operators->'jackal'->>'timePlayed')::numeric AS jackal_time_played,
(p2.operators->'jackal'->>'timePlayed')::numeric - (p1.operators->'jackal'->>'timePlayed')::numeric AS jackal_time_played_delta,
(p2.operators->'jackal'->>'kills')::numeric AS jackal_kills,
(p2.operators->'jackal'->>'kills')::numeric - (p1.operators->'jackal'->>'kills')::numeric AS jackal_kills_delta,
(p2.operators->'jackal'->>'deaths')::numeric AS jackal_deaths,
(p2.operators->'jackal'->>'deaths')::numeric - (p1.operators->'jackal'->>'deaths')::numeric AS jackal_deaths_delta,
(p2.operators->'jackal'->>'won')::numeric AS jackal_won,
(p2.operators->'jackal'->>'won')::numeric - (p1.operators->'jackal'->>'won')::numeric AS jackal_won_delta,
(p2.operators->'jackal'->>'lost')::numeric AS jackal_lost,
(p2.operators->'jackal'->>'lost')::numeric - (p1.operators->'jackal'->>'lost')::numeric AS jackal_lost_delta,
(p2.operators->'kapkan'->>'timePlayed')::numeric AS kapkan_time_played,
(p2.operators->'kapkan'->>'timePlayed')::numeric - (p1.operators->'kapkan'->>'timePlayed')::numeric AS kapkan_time_played_delta,
(p2.operators->'kapkan'->>'kills')::numeric AS kapkan_kills,
(p2.operators->'kapkan'->>'kills')::numeric - (p1.operators->'kapkan'->>'kills')::numeric AS kapkan_kills_delta,
(p2.operators->'kapkan'->>'deaths')::numeric AS kapkan_deaths,
(p2.operators->'kapkan'->>'deaths')::numeric - (p1.operators->'kapkan'->>'deaths')::numeric AS kapkan_deaths_delta,
(p2.operators->'kapkan'->>'won')::numeric AS kapkan_won,
(p2.operators->'kapkan'->>'won')::numeric - (p1.operators->'kapkan'->>'won')::numeric AS kapkan_won_delta,
(p2.operators->'kapkan'->>'lost')::numeric AS kapkan_lost,
(p2.operators->'kapkan'->>'lost')::numeric - (p1.operators->'kapkan'->>'lost')::numeric AS kapkan_lost_delta,
(p2.operators->'sledge'->>'timePlayed')::numeric AS sledge_time_played,
(p2.operators->'sledge'->>'timePlayed')::numeric - (p1.operators->'sledge'->>'timePlayed')::numeric AS sledge_time_played_delta,
(p2.operators->'sledge'->>'kills')::numeric AS sledge_kills,
(p2.operators->'sledge'->>'kills')::numeric - (p1.operators->'sledge'->>'kills')::numeric AS sledge_kills_delta,
(p2.operators->'sledge'->>'deaths')::numeric AS sledge_deaths,
(p2.operators->'sledge'->>'deaths')::numeric - (p1.operators->'sledge'->>'deaths')::numeric AS sledge_deaths_delta,
(p2.operators->'sledge'->>'won')::numeric AS sledge_won,
(p2.operators->'sledge'->>'won')::numeric - (p1.operators->'sledge'->>'won')::numeric AS sledge_won_delta,
(p2.operators->'sledge'->>'lost')::numeric AS sledge_lost,
(p2.operators->'sledge'->>'lost')::numeric - (p1.operators->'sledge'->>'lost')::numeric AS sledge_lost_delta,
(p2.operators->'twitch'->>'timePlayed')::numeric AS twitch_time_played,
(p2.operators->'twitch'->>'timePlayed')::numeric - (p1.operators->'twitch'->>'timePlayed')::numeric AS twitch_time_played_delta,
(p2.operators->'twitch'->>'kills')::numeric AS twitch_kills,
(p2.operators->'twitch'->>'kills')::numeric - (p1.operators->'twitch'->>'kills')::numeric AS twitch_kills_delta,
(p2.operators->'twitch'->>'deaths')::numeric AS twitch_deaths,
(p2.operators->'twitch'->>'deaths')::numeric - (p1.operators->'twitch'->>'deaths')::numeric AS twitch_deaths_delta,
(p2.operators->'twitch'->>'won')::numeric AS twitch_won,
(p2.operators->'twitch'->>'won')::numeric - (p1.operators->'twitch'->>'won')::numeric AS twitch_won_delta,
(p2.operators->'twitch'->>'lost')::numeric AS twitch_lost,
(p2.operators->'twitch'->>'lost')::numeric - (p1.operators->'twitch'->>'lost')::numeric AS twitch_lost_delta,
(p2.operators->'capitao'->>'timePlayed')::numeric AS capitao_time_played,
(p2.operators->'capitao'->>'timePlayed')::numeric - (p1.operators->'capitao'->>'timePlayed')::numeric AS capitao_time_played_delta,
(p2.operators->'capitao'->>'kills')::numeric AS capitao_kills,
(p2.operators->'capitao'->>'kills')::numeric - (p1.operators->'capitao'->>'kills')::numeric AS capitao_kills_delta,
(p2.operators->'capitao'->>'deaths')::numeric AS capitao_deaths,
(p2.operators->'capitao'->>'deaths')::numeric - (p1.operators->'capitao'->>'deaths')::numeric AS capitao_deaths_delta,
(p2.operators->'capitao'->>'won')::numeric AS capitao_won,
(p2.operators->'capitao'->>'won')::numeric - (p1.operators->'capitao'->>'won')::numeric AS capitao_won_delta,
(p2.operators->'capitao'->>'lost')::numeric AS capitao_lost,
(p2.operators->'capitao'->>'lost')::numeric - (p1.operators->'capitao'->>'lost')::numeric AS capitao_lost_delta,
(p2.operators->'caveira'->>'timePlayed')::numeric AS caveira_time_played,
(p2.operators->'caveira'->>'timePlayed')::numeric - (p1.operators->'caveira'->>'timePlayed')::numeric AS caveira_time_played_delta,
(p2.operators->'caveira'->>'kills')::numeric AS caveira_kills,
(p2.operators->'caveira'->>'kills')::numeric - (p1.operators->'caveira'->>'kills')::numeric AS caveira_kills_delta,
(p2.operators->'caveira'->>'deaths')::numeric AS caveira_deaths,
(p2.operators->'caveira'->>'deaths')::numeric - (p1.operators->'caveira'->>'deaths')::numeric AS caveira_deaths_delta,
(p2.operators->'caveira'->>'won')::numeric AS caveira_won,
(p2.operators->'caveira'->>'won')::numeric - (p1.operators->'caveira'->>'won')::numeric AS caveira_won_delta,
(p2.operators->'caveira'->>'lost')::numeric AS caveira_lost,
(p2.operators->'caveira'->>'lost')::numeric - (p1.operators->'caveira'->>'lost')::numeric AS caveira_lost_delta,
(p2.operators->'montagne'->>'timePlayed')::numeric AS montagne_time_played,
(p2.operators->'montagne'->>'timePlayed')::numeric - (p1.operators->'montagne'->>'timePlayed')::numeric AS montagne_time_played_delta,
(p2.operators->'montagne'->>'kills')::numeric AS montagne_kills,
(p2.operators->'montagne'->>'kills')::numeric - (p1.operators->'montagne'->>'kills')::numeric AS montagne_kills_delta,
(p2.operators->'montagne'->>'deaths')::numeric AS montagne_deaths,
(p2.operators->'montagne'->>'deaths')::numeric - (p1.operators->'montagne'->>'deaths')::numeric AS montagne_deaths_delta,
(p2.operators->'montagne'->>'won')::numeric AS montagne_won,
(p2.operators->'montagne'->>'won')::numeric - (p1.operators->'montagne'->>'won')::numeric AS montagne_won_delta,
(p2.operators->'montagne'->>'lost')::numeric AS montagne_lost,
(p2.operators->'montagne'->>'lost')::numeric - (p1.operators->'montagne'->>'lost')::numeric AS montagne_lost_delta,
(p2.operators->'tachanka'->>'timePlayed')::numeric AS tachanka_time_played,
(p2.operators->'tachanka'->>'timePlayed')::numeric - (p1.operators->'tachanka'->>'timePlayed')::numeric AS tachanka_time_played_delta,
(p2.operators->'tachanka'->>'kills')::numeric AS tachanka_kills,
(p2.operators->'tachanka'->>'kills')::numeric - (p1.operators->'tachanka'->>'kills')::numeric AS tachanka_kills_delta,
(p2.operators->'tachanka'->>'deaths')::numeric AS tachanka_deaths,
(p2.operators->'tachanka'->>'deaths')::numeric - (p1.operators->'tachanka'->>'deaths')::numeric AS tachanka_deaths_delta,
(p2.operators->'tachanka'->>'won')::numeric AS tachanka_won,
(p2.operators->'tachanka'->>'won')::numeric - (p1.operators->'tachanka'->>'won')::numeric AS tachanka_won_delta,
(p2.operators->'tachanka'->>'lost')::numeric AS tachanka_lost,
(p2.operators->'tachanka'->>'lost')::numeric - (p1.operators->'tachanka'->>'lost')::numeric AS tachanka_lost_delta,
(p2.operators->'thatcher'->>'timePlayed')::numeric AS thatcher_time_played,
(p2.operators->'thatcher'->>'timePlayed')::numeric - (p1.operators->'thatcher'->>'timePlayed')::numeric AS thatcher_time_played_delta,
(p2.operators->'thatcher'->>'kills')::numeric AS thatcher_kills,
(p2.operators->'thatcher'->>'kills')::numeric - (p1.operators->'thatcher'->>'kills')::numeric AS thatcher_kills_delta,
(p2.operators->'thatcher'->>'deaths')::numeric AS thatcher_deaths,
(p2.operators->'thatcher'->>'deaths')::numeric - (p1.operators->'thatcher'->>'deaths')::numeric AS thatcher_deaths_delta,
(p2.operators->'thatcher'->>'won')::numeric AS thatcher_won,
(p2.operators->'thatcher'->>'won')::numeric - (p1.operators->'thatcher'->>'won')::numeric AS thatcher_won_delta,
(p2.operators->'thatcher'->>'lost')::numeric AS thatcher_lost,
(p2.operators->'thatcher'->>'lost')::numeric - (p1.operators->'thatcher'->>'lost')::numeric AS thatcher_lost_delta,
(p2.operators->'thermite'->>'timePlayed')::numeric AS thermite_time_played,
(p2.operators->'thermite'->>'timePlayed')::numeric - (p1.operators->'thermite'->>'timePlayed')::numeric AS thermite_time_played_delta,
(p2.operators->'thermite'->>'kills')::numeric AS thermite_kills,
(p2.operators->'thermite'->>'kills')::numeric - (p1.operators->'thermite'->>'kills')::numeric AS thermite_kills_delta,
(p2.operators->'thermite'->>'deaths')::numeric AS thermite_deaths,
(p2.operators->'thermite'->>'deaths')::numeric - (p1.operators->'thermite'->>'deaths')::numeric AS thermite_deaths_delta,
(p2.operators->'thermite'->>'won')::numeric AS thermite_won,
(p2.operators->'thermite'->>'won')::numeric - (p1.operators->'thermite'->>'won')::numeric AS thermite_won_delta,
(p2.operators->'thermite'->>'lost')::numeric AS thermite_lost,
(p2.operators->'thermite'->>'lost')::numeric - (p1.operators->'thermite'->>'lost')::numeric AS thermite_lost_delta,
(p2.operators->'valkyrie'->>'timePlayed')::numeric AS valkyrie_time_played,
(p2.operators->'valkyrie'->>'timePlayed')::numeric - (p1.operators->'valkyrie'->>'timePlayed')::numeric AS valkyrie_time_played_delta,
(p2.operators->'valkyrie'->>'kills')::numeric AS valkyrie_kills,
(p2.operators->'valkyrie'->>'kills')::numeric - (p1.operators->'valkyrie'->>'kills')::numeric AS valkyrie_kills_delta,
(p2.operators->'valkyrie'->>'deaths')::numeric AS valkyrie_deaths,
(p2.operators->'valkyrie'->>'deaths')::numeric - (p1.operators->'valkyrie'->>'deaths')::numeric AS valkyrie_deaths_delta,
(p2.operators->'valkyrie'->>'won')::numeric AS valkyrie_won,
(p2.operators->'valkyrie'->>'won')::numeric - (p1.operators->'valkyrie'->>'won')::numeric AS valkyrie_won_delta,
(p2.operators->'valkyrie'->>'lost')::numeric AS valkyrie_lost,
(p2.operators->'valkyrie'->>'lost')::numeric - (p1.operators->'valkyrie'->>'lost')::numeric AS valkyrie_lost_delta,
(p2.operators->'blackbeard'->>'timePlayed')::numeric AS blackbeard_time_played,
(p2.operators->'blackbeard'->>'timePlayed')::numeric - (p1.operators->'blackbeard'->>'timePlayed')::numeric AS blackbeard_time_played_delta,
(p2.operators->'blackbeard'->>'kills')::numeric AS blackbeard_kills,
(p2.operators->'blackbeard'->>'kills')::numeric - (p1.operators->'blackbeard'->>'kills')::numeric AS blackbeard_kills_delta,
(p2.operators->'blackbeard'->>'deaths')::numeric AS blackbeard_deaths,
(p2.operators->'blackbeard'->>'deaths')::numeric - (p1.operators->'blackbeard'->>'deaths')::numeric AS blackbeard_deaths_delta,
(p2.operators->'blackbeard'->>'won')::numeric AS blackbeard_won,
(p2.operators->'blackbeard'->>'won')::numeric - (p1.operators->'blackbeard'->>'won')::numeric AS blackbeard_won_delta,
(p2.operators->'blackbeard'->>'lost')::numeric AS blackbeard_lost,
(p2.operators->'blackbeard'->>'lost')::numeric - (p1.operators->'blackbeard'->>'lost')::numeric AS blackbeard_lost_delta
FROM progressions p1
LEFT JOIN progressions p2 ON p1.player_id = p2.player_id AND p2.relevant_at = '2017-09-18'
WHERE p1.relevant_at = '2017-09-17'
) t) y
ON CONFLICT(relevant_at)
DO UPDATE
SET(operators, updated_at) = (excluded.operators, NOW())
WHERE community_operators.relevant_at = excluded.relevant_at;
Small update: we decided to break this into different samples, one sample for the whole community and the other for diamond-only population
Could this also lead to a dedicate stats giving the pick-rate of the operators by seasons somehow?
on the new server possibly. we currently don't have enough hdd space to hold a seasons worth of comstats
I had an other idea, is that possible to have a leaderboard by operators?
Something like TOP-25 Blitz players
- with a minium 100 games with the operator
- formula with a combination of K/D, Win%, Survival, K/Round
It would also be nice is the leaderboard could display Global Win% and K/D with the Skill Rating
Needs some changes to the backend API, let's do community ranks first