diff options
| author | schneefux <schneefux+commit@schneefux.xyz> | 2017-01-27 15:11:51 +0100 |
|---|---|---|
| committer | schneefux <schneefux+commit@schneefux.xyz> | 2017-01-27 15:11:51 +0100 |
| commit | 9d995e47fd743f1a8ba18ee9daac7e822ad63660 (patch) | |
| tree | 0ecb268183c921b22db268d8799c12eb44788a8b /queries/hero-winrates.sql | |
| parent | 16ba2a3e926105b2dd732e8cb28eddcdda1d1fc2 (diff) | |
| download | apigrabber-9d995e47fd743f1a8ba18ee9daac7e822ad63660.tar.gz apigrabber-9d995e47fd743f1a8ba18ee9daac7e822ad63660.zip | |
api: /winrates endpoint
Diffstat (limited to 'queries/hero-winrates.sql')
| -rw-r--r-- | queries/hero-winrates.sql | 18 |
1 files changed, 18 insertions, 0 deletions
diff --git a/queries/hero-winrates.sql b/queries/hero-winrates.sql new file mode 100644 index 0000000..51f40e1 --- /dev/null +++ b/queries/hero-winrates.sql @@ -0,0 +1,18 @@ +SELECT ARRAY_TO_JSON(ARRAY( +SELECT + JSONB_BUILD_OBJECT( + 'actor', actors.actor, + 'winrate', (SELECT + SUM((CASE WHEN (participant.data->'attributes'->'stats'->>'winner')='true' THEN 1 ELSE 0 END))::float + / + COUNT(*)::float + FROM participant + WHERE (participant.data->'attributes'->>'actor') = actors.actor) + ) +FROM +(SELECT + DISTINCT participant.data->'attributes'->>'actor' + AS actor + FROM participant) +AS actors) +) AS data;
\ No newline at end of file |