diff options
| author | schneefux <schneefux+commit@schneefux.xyz> | 2017-02-28 19:06:44 +0100 |
|---|---|---|
| committer | schneefux <schneefux+commit@schneefux.xyz> | 2017-02-28 19:06:44 +0100 |
| commit | 4a6f14706e5b837c1ffeaafeaceac2f9971d1420 (patch) | |
| tree | 4c89134158bc058b5617fb7e7199404fe905c31e | |
| parent | cfa29e50a193137ec7f47fa82429035dfa4a81e7 (diff) | |
| download | compiler-4a6f14706e5b837c1ffeaafeaceac2f9971d1420.tar.gz compiler-4a6f14706e5b837c1ffeaafeaceac2f9971d1420.zip | |
add untested example query
| -rw-r--r-- | queries/player/count_player_matches.sql | 16 |
1 files changed, 16 insertions, 0 deletions
diff --git a/queries/player/count_player_matches.sql b/queries/player/count_player_matches.sql new file mode 100644 index 0000000..f7bd29f --- /dev/null +++ b/queries/player/count_player_matches.sql @@ -0,0 +1,16 @@ +-- UNTESTED! +WITH plr AS ( + SELECT + "id", + "apiId", + -- count matches in db + COUNT(DISTINCT participant."apiId") AS "games_in_db" + FROM player + JOIN participant ON player."apiId" = participant."player_apiId" + JOIN roster ON participant."roster_apiId" = roster."apiId" + WHERE player."apiId"=$1 +) +INSERT INTO player_stats("id", "apiId", "games_in_db") + SELECT * FROM plr +ON CONFLICT("apiId") DO +UPDATE SET "games_in_db"=plr."games_in_db" |