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 | |
| parent | 16ba2a3e926105b2dd732e8cb28eddcdda1d1fc2 (diff) | |
| download | apigrabber-9d995e47fd743f1a8ba18ee9daac7e822ad63660.tar.gz apigrabber-9d995e47fd743f1a8ba18ee9daac7e822ad63660.zip | |
api: /winrates endpoint
Diffstat (limited to 'queries')
| -rw-r--r-- | queries/hero-winrates.sql | 18 | ||||
| -rw-r--r-- | queries/recent-matches.sql | 6 |
2 files changed, 21 insertions, 3 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 diff --git a/queries/recent-matches.sql b/queries/recent-matches.sql index fb237d7..9295813 100644 --- a/queries/recent-matches.sql +++ b/queries/recent-matches.sql @@ -1,7 +1,7 @@ -SELECT ARRAY_TO_JSON(ARRAY( +SELECT ARRAY_TO_JSON(ARRAY( SELECT JSONB_BUILD_OBJECT( - 'id', match.data->>'id', + 'id', (match.data->'attributes'->>'shardId') || (match.data->>'id'), 'date', match.data->'attributes'->>'createdAt', 'duration', CAST( match.data->'attributes'->>'duration' @@ -27,4 +27,4 @@ SELECT ) FROM match ORDER BY match.data->'attributes'->>'createdAt' DESC -)) AS data +)) AS data;
\ No newline at end of file |