diff options
Diffstat (limited to 'queries')
| -rw-r--r-- | queries/participant/stats.sql | 13 |
1 files changed, 13 insertions, 0 deletions
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) |