From 9d995e47fd743f1a8ba18ee9daac7e822ad63660 Mon Sep 17 00:00:00 2001
From: schneefux <schneefux+commit@schneefux.xyz>
Date: Fri, 27 Jan 2017 15:11:51 +0100
Subject: api: /winrates endpoint

---
 queries/hero-winrates.sql | 18 ++++++++++++++++++
 1 file changed, 18 insertions(+)
 create mode 100644 queries/hero-winrates.sql

(limited to 'queries/hero-winrates.sql')

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
-- 
cgit v1.3.1