api: /winrates endpoint

3 files changed, 26 insertions, 3 deletions

diff --git a/api.py b/api.py
index bbd173a..44c47ba 100644
--- a/api.py
+++ b/api.py
@@ -54,6 +54,11 @@ async def api_matches(request):
     data = (await db.select(queries.queries["recent-matches"]))[0]["data"]
     return aiohttp.web.Response(text=str(data))
 
+@route("/winrates")
+async def api_winrates(request):
+    data = (await db.select(queries.queries["hero-winrates"]))[0]["data"]
+    return aiohttp.web.Response(text=str(data))
+
 @route("/status")
 async def api_status(_):
     resp = json.dumps({"version": "0.1.0"})
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