From 0a3421d87d7e55707062f1186cf64f2c54734444 Mon Sep 17 00:00:00 2001
From: schneefux <schneefux+commit@schneefux.xyz>
Date: Tue, 14 Mar 2017 18:47:32 +0100
Subject: add participant_stats query (wip)

---
 queries/participant/stats.sql | 13 +++++++++++++
 1 file changed, 13 insertions(+)
 create mode 100644 queries/participant/stats.sql

(limited to 'queries/participant/stats.sql')

diff --git a/queries/participant/stats.sql b/queries/participant/stats.sql
new file mode 100644
index 0000000..05a9697
--- /dev/null
+++ b/queries/participant/stats.sql
@@ -0,0 +1,13 @@
+WITH stats AS (
+    SELECT
+        api_id,
+        CASE WHEN roster.hero_kills=0
+        THEN 0
+        ELSE (kills+assists)/roster.hero_kills::float
+        END AS kill_participation
+    FROM participant JOIN roster ON roster.api_id=participant.roster_api_id
+)
+INSERT INTO participant_stats(participant_api_id, kill_participation)
+SELECT * FROM stats
+ON CONFLICT(participant_api_id) DO
+UPDATE SET(participant_api_id, kill_participation) = (SELECT * FROM stats)
-- 
cgit v1.3.1