diff options
Diffstat (limited to 'queries/player.sql')
| -rw-r--r-- | queries/player.sql | 34 |
1 files changed, 24 insertions, 10 deletions
diff --git a/queries/player.sql b/queries/player.sql index 992cb44..0619ee6 100644 --- a/queries/player.sql +++ b/queries/player.sql @@ -1,15 +1,29 @@ +WITH rosters AS (SELECT + match.data->'data'->'attributes'->>'createdAt' AS matchdate, + JSONB_ARRAY_ELEMENTS(match.data->'relations') AS roster +FROM match WHERE id=$1), +participants AS (SELECT + matchdate, + JSONB_ARRAY_ELEMENTS(rosters.roster->'relations') AS participant +FROM rosters), +players AS (SELECT + matchdate, + JSONB_ARRAY_ELEMENTS(participants.participant->'relations') AS player +FROM participants) + SELECT -id AS "apiId", -attributes->>'name' AS "name", -(attributes->'stats'->>'level')::int AS "level", -(attributes->'stats'->>'xp')::int AS "xp", -(attributes->'stats'->>'played')::int AS "played", -(attributes->'stats'->>'played_ranked')::int AS "playedRanked", -(attributes->'stats'->>'played')::int - (attributes->'stats'->>'played_ranked')::int AS "playedCasual", -(attributes->'stats'->>'wins')::int AS "wins", -(attributes->'stats'->>'lifetimeGold')::float AS "lifetimeGold", +players.player->'data'->>'id' AS "apiId", +players.player->'data'->'attributes'->>'name' AS "name", +(players.player->'data'->'attributes'->'stats'->>'level')::int AS "level", +(players.player->'data'->'attributes'->'stats'->>'xp')::int AS "xp", +(players.player->'data'->'attributes'->'stats'->>'played')::int AS "played", +(players.player->'data'->'attributes'->'stats'->>'played_ranked')::int AS "playedRanked", +(players.player->'data'->'attributes'->'stats'->>'played')::int - (players.player->'data'->'attributes'->'stats'->>'played_ranked')::int AS "playedCasual", +(players.player->'data'->'attributes'->'stats'->>'wins')::int AS "wins", +(players.player->'data'->'attributes'->'stats'->>'lifetimeGold')::float AS "lifetimeGold", +CASE WHEN players.player->'data'->'attributes'->>'name'=$2 THEN matchdate ELSE 'epoch'::timestamp::text END AS "lastMatchCreatedDate", 0 AS "streak" -FROM "player" +FROM players |