diff options
| author | schneefux <schneefux+commit@schneefux.xyz> | 2017-03-14 18:47:32 +0100 |
|---|---|---|
| committer | schneefux <schneefux+commit@schneefux.xyz> | 2017-03-14 18:47:32 +0100 |
| commit | 0a3421d87d7e55707062f1186cf64f2c54734444 (patch) | |
| tree | 1cb65d2ec8f406c6ef2177f3cf0880ded9b8355b | |
| parent | e2d4663729273477817533537320ca856e2400c3 (diff) | |
| download | compiler-0a3421d87d7e55707062f1186cf64f2c54734444.tar.gz compiler-0a3421d87d7e55707062f1186cf64f2c54734444.zip | |
add participant_stats query (wip)
| -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) |