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

1 files changed, 24 insertions, 15 deletions

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