From 4a6f14706e5b837c1ffeaafeaceac2f9971d1420 Mon Sep 17 00:00:00 2001
From: schneefux <schneefux+commit@schneefux.xyz>
Date: Tue, 28 Feb 2017 19:06:44 +0100
Subject: add untested example query

---
 queries/player/count_player_matches.sql | 16 ++++++++++++++++
 1 file changed, 16 insertions(+)
 create mode 100644 queries/player/count_player_matches.sql

diff --git a/queries/player/count_player_matches.sql b/queries/player/count_player_matches.sql
new file mode 100644
index 0000000..f7bd29f
--- /dev/null
+++ b/queries/player/count_player_matches.sql
@@ -0,0 +1,16 @@
+-- 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
+)
+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"
-- 
cgit v1.3.1