From 90d5f8edb1d5a6cb95a18c48eca98e93cf4d34b7 Mon Sep 17 00:00:00 2001
From: Kapil Viren Ahuja <k.v.ahuja@gmail.com>
Date: Wed, 1 Mar 2017 20:07:31 +0530
Subject: Some stats calc - we need to add patch version in some core web
 tables.

---
 queries/player/count_player_matches.sql | 39 ++++++++++++++++++++-------------
 1 file changed, 24 insertions(+), 15 deletions(-)

(limited to 'queries/player/count_player_matches.sql')

diff --git a/queries/player/count_player_matches.sql b/queries/player/count_player_matches.sql
index f7bd29f..2bb549d 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" = '6eab29f6-eed5-11e6-ba5f-068789513eb5')
+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

