From dfa0872dd7940b6d2759d8901274f459edf1b46e Mon Sep 17 00:00:00 2001 From: schneefux Date: Tue, 28 Mar 2017 18:30:06 +0200 Subject: rewrite in NodeJS --- queries/player.sql | 34 ---------------------------------- 1 file changed, 34 deletions(-) delete mode 100644 queries/player.sql (limited to 'queries/player.sql') diff --git a/queries/player.sql b/queries/player.sql deleted file mode 100644 index d718391..0000000 --- a/queries/player.sql +++ /dev/null @@ -1,34 +0,0 @@ -WITH rosters AS (SELECT - match.data->'data'->'attributes'->>'shardId' AS shardid, - (match.data->'data'->'attributes'->>'createdAt')::timestamp AS matchdate, - JSONB_ARRAY_ELEMENTS(match.data->'relations') AS roster -FROM match WHERE id=$1), -participants AS (SELECT - shardid, - matchdate, - JSONB_ARRAY_ELEMENTS(rosters.roster->'relations') AS participant -FROM rosters), -players AS (SELECT - participants.participant->'data'->'attributes'->'stats'->>'skillTier' AS skill_tier, - shardid, - matchdate, - JSONB_ARRAY_ELEMENTS(participants.participant->'relations') AS player -FROM participants) - -SELECT - -players.player->'data'->>'id' AS "api_id", -shardid AS "shard_id", -players.player->'data'->'attributes'->>'name' AS "name", -(players.player->'data'->'attributes'->'stats'->>'level')::int AS "level", -(players.player->'data'->'attributes'->'stats'->>'xp')::float::int AS "xp", -(players.player->'data'->'attributes'->'stats'->>'played')::int AS "played", -(players.player->'data'->'attributes'->'stats'->>'played_ranked')::int AS "played_ranked", -(players.player->'data'->'attributes'->'stats'->>'played')::int - (players.player->'data'->'attributes'->'stats'->>'played_ranked')::int AS "played_casual", -(players.player->'data'->'attributes'->'stats'->>'wins')::int AS "wins", -(players.player->'data'->'attributes'->'stats'->>'lifetimeGold')::float AS "lifetime_gold", -matchdate AS "last_match_created_date", -skill_tier AS "skill_tier", -0 AS "streak" - -FROM players -- cgit v1.3.1