diff options
| author | kvahuja <k.v.ahuja@gmail.com> | 2017-02-18 15:42:48 +0530 |
|---|---|---|
| committer | kvahuja <k.v.ahuja@gmail.com> | 2017-02-18 15:42:48 +0530 |
| commit | 22add9e90e2c62a3a08d64b8c73a66b8de77bc97 (patch) | |
| tree | d77b0a7a6e2425b1f2c27178edeb75b1d38dbbe8 /queries/participant.sql | |
| parent | b7e442270e0427c8ebf0d246edd91c26d1ad37a1 (diff) | |
| download | processor-22add9e90e2c62a3a08d64b8c73a66b8de77bc97.tar.gz processor-22add9e90e2c62a3a08d64b8c73a66b8de77bc97.zip | |
refactoring as part of https://github.com/vainglorygame/vainsocial.com/issues/17
Diffstat (limited to 'queries/participant.sql')
| -rw-r--r-- | queries/participant.sql | 28 |
1 files changed, 28 insertions, 0 deletions
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 |