diff options
| -rw-r--r-- | queries/participant/stats.sql | 10 |
1 files changed, 7 insertions, 3 deletions
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) |