From 22add9e90e2c62a3a08d64b8c73a66b8de77bc97 Mon Sep 17 00:00:00 2001
From: kvahuja <k.v.ahuja@gmail.com>
Date: Sat, 18 Feb 2017 15:42:48 +0530
Subject: refactoring as part of
 https://github.com/vainglorygame/vainsocial.com/issues/17

---
 queries/participant.sql | 28 ++++++++++++++++++++++++++++
 1 file changed, 28 insertions(+)
 create mode 100644 queries/participant.sql

(limited to 'queries/participant.sql')

diff --git a/queries/participant.sql b/queries/participant.sql
new file mode 100644
index 0000000..a84274d
--- /dev/null
+++ b/queries/participant.sql
@@ -0,0 +1,28 @@
+SELECT
+
+id AS "apiId",
+relationships->'player'->'data'->>'id' AS "playerId",
+
+attributes->>'actor' AS "hero",
+(attributes->'stats'->>'assists')::int AS "assists",
+COALESCE(NULLIF(attributes->'stats'->>'crystalMineCaptures', ''), '0')::int AS "crystalMineCaptures",
+(attributes->'stats'->>'deaths')::int AS "deaths",
+(attributes->'stats'->>'farm')::float AS "farm",
+(attributes->'stats'->>'firstAfkTime')::float AS "firstAfkTime",
+COALESCE(NULLIF(attributes->'stats'->>'goldMineCaptures', ''), '0')::int AS "goldMineCaptures",
+COALESCE(NULLIF(attributes->'stats'->>'jungleKills', ''), '0')::int AS "jungleKills",
+COALESCE(NULLIF(attributes->'stats'->>'karmaLevel', ''), '0')::int AS "karmaLevel",
+COALESCE(NULLIF(attributes->'stats'->>'kills', ''), '0')::int AS "kills",
+COALESCE(NULLIF(attributes->'stats'->>'krakenCaptures', ''), '0')::int AS "krakenCaptures",
+COALESCE(NULLIF(attributes->'stats'->>'level', ''), '0')::int AS "level",
+COALESCE(NULLIF(attributes->'stats'->>'minionKills', ''), '0')::int AS "minionKills",
+COALESCE(NULLIF(attributes->'stats'->>'skillTier', ''), '0')::int AS "skillTier",
+COALESCE(NULLIF(attributes->'stats'->>'skinKey', ''), '0') AS "skinKey",
+(attributes->'stats'->>'wentAfk')::bool::bool AS "wentAfk",
+(attributes->'stats'->>'winner')::bool::bool AS "winner",
+attributes->'stats'->'itemGrants' AS "itemGrants",
+attributes->'stats'->'itemSells' AS "itemSells",
+attributes->'stats'->'itemUses' AS "itemUses",
+attributes->'stats'->'items' AS "items"
+
+FROM participant
-- 
cgit v1.3.1

