From 65397e311f6878a0380a0ad2e2f42c20c06890e2 Mon Sep 17 00:00:00 2001 From: Kapil Viren Ahuja Date: Thu, 2 Mar 2017 21:20:12 +0530 Subject: Some stats calc - we need to add patch version in some core web tables. (#5) * Some stats calc - we need to add patch version in some core web tables. * fixed player id as parameter --- queries/player/count_player_matches.sql | 39 ++++++++++++++++++++------------- 1 file changed, 24 insertions(+), 15 deletions(-) (limited to 'queries/player') diff --git a/queries/player/count_player_matches.sql b/queries/player/count_player_matches.sql index f7bd29f..ddfbb66 100644 --- a/queries/player/count_player_matches.sql +++ b/queries/player/count_player_matches.sql @@ -1,16 +1,25 @@ --- UNTESTED! -WITH plr AS ( - SELECT - "id", - "apiId", - -- count matches in db - COUNT(DISTINCT participant."apiId") AS "games_in_db" - FROM player - JOIN participant ON player."apiId" = participant."player_apiId" - JOIN roster ON participant."roster_apiId" = roster."apiId" - WHERE player."apiId"=$1 +with stats as ( +with plr as ( +select + 'a' as patch_version, + player."apiId" as player_api_id, + match."gameMode" as game_mode, + participant."winner" as winner +from + match, roster, participant, player +where + match."apiId" = roster."match_apiId" and + roster."apiId" = participant."roster_apiId" and + participant."player_apiId" = player."apiId" and + player."apiId" = $1) +select + (select distinct(player_api_id) 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) ) -INSERT INTO player_stats("id", "apiId", "games_in_db") - SELECT * FROM plr -ON CONFLICT("apiId") DO -UPDATE SET "games_in_db"=plr."games_in_db" +INSERT INTO player_stats("player_apiId", played, wins, casual_played, casual_wins, ranked_played, ranked_wins) + SELECT * FROM stats -- cgit v1.3.1