diff options
Diffstat (limited to 'queries')
| -rw-r--r-- | queries/participant/stats.sql | 21 | ||||
| -rw-r--r-- | queries/player/count_player_matches.sql | 29 |
2 files changed, 0 insertions, 50 deletions
diff --git a/queries/participant/stats.sql b/queries/participant/stats.sql deleted file mode 100644 index fe4025f..0000000 --- a/queries/participant/stats.sql +++ /dev/null @@ -1,21 +0,0 @@ -WITH stats AS ( - SELECT - participant.api_id, - match.patch_version, - CASE WHEN roster.hero_kills=0 - THEN 0 - ELSE (kills+assists)/roster.hero_kills::float - END AS kills_participation, - CASE WHEN participant.deaths=0 - THEN 0 - ELSE (kills+assists)/deaths::float - END AS kda - FROM participant - JOIN roster ON roster.api_id=participant.roster_api_id - JOIN match ON match.api_id=roster.match_api_id - WHERE participant.api_id = $1 -) -INSERT INTO participant_stats(participant_api_id, patch_version, kills_participation, kda) -SELECT * FROM stats -ON CONFLICT(participant_api_id) DO -UPDATE SET(participant_api_id, patch_version, kills_participation, kda) = (SELECT * FROM stats) diff --git a/queries/player/count_player_matches.sql b/queries/player/count_player_matches.sql deleted file mode 100644 index 226cacc..0000000 --- a/queries/player/count_player_matches.sql +++ /dev/null @@ -1,29 +0,0 @@ -with stats as ( -with plr as ( -select - match.patch_version as patch_version, - player.api_id as player_api_id, - match.game_mode as game_mode, - participant.winner as winner -from - match, roster, participant, player -where - match.api_id = roster.match_api_id and - roster.api_id = participant.roster_api_id and - participant.player_api_id = player.api_id and - player.api_id = $1) -select - (select distinct(player_api_id) from plr), - (select distinct(patch_version) from plr), - (select count(*) as played from plr), - (select count(*) as wins from plr where winner = true), - (select count(*) as casual_played from plr where game_mode like '%casual%'), - (select count(*) as casual_wins from plr where game_mode like '%casual%' and winner = true), - (select count(*) as ranked_played from plr where game_mode like '%ranked%'), - (select count(*) as ranked_wins from plr where game_mode like '%ranked%' and winner = true), - (select 0 as streak) -) -INSERT INTO player_stats(player_api_id, patch_version, played, wins, casual_played, casual_wins, ranked_played, ranked_wins, streak) - SELECT * FROM stats -ON CONFLICT(player_api_id) DO - UPDATE SET (player_api_id, patch_version, played, wins, casual_played, casual_wins, ranked_played, ranked_wins, streak) = (SELECT * FROM stats) |