From 1a7f0eb91529148b08eec58ce9cbebf514a9af25 Mon Sep 17 00:00:00 2001
From: schneefux <schneefux+commit@schneefux.xyz>
Date: Wed, 15 Mar 2017 14:47:08 +0100
Subject: calculate kda ratio (fix vainglorygame/meta#51)

---
 queries/participant/stats.sql | 10 +++++++---
 1 file changed, 7 insertions(+), 3 deletions(-)

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

diff --git a/queries/participant/stats.sql b/queries/participant/stats.sql
index 6a6d540..fe4025f 100644
--- a/queries/participant/stats.sql
+++ b/queries/participant/stats.sql
@@ -5,13 +5,17 @@ WITH stats AS (
         CASE WHEN roster.hero_kills=0
         THEN 0
         ELSE (kills+assists)/roster.hero_kills::float
-        END AS kills_participation
+        END AS kills_participation,
+        CASE WHEN participant.deaths=0
+        THEN 0
+        ELSE (kills+assists)/deaths::float
+        END AS kda
     FROM participant
     JOIN roster ON roster.api_id=participant.roster_api_id
     JOIN match ON match.api_id=roster.match_api_id
     WHERE participant.api_id = $1
 )
-INSERT INTO participant_stats(participant_api_id, patch_version, kills_participation)
+INSERT INTO participant_stats(participant_api_id, patch_version, kills_participation, kda)
 SELECT * FROM stats
 ON CONFLICT(participant_api_id) DO
-UPDATE SET(participant_api_id, patch_version, kills_participation) = (SELECT * FROM stats)
+UPDATE SET(participant_api_id, patch_version, kills_participation, kda) = (SELECT * FROM stats)
-- 
cgit v1.3.1

