summaryrefslogtreecommitdiff
path: root/sql
diff options
context:
space:
mode:
authorDennis Kobert <dennis@kobert.dev>2021-02-19 11:07:39 +0000
committerDennis Kobert <dennis@kobert.dev>2021-02-19 11:07:39 +0000
commita3dda94db49570f7f1e6484b49bca9196c317493 (patch)
tree07519190d8cfbf76ba7ddf2b170f008ea652f83c /sql
parentbc5da83a645c22e10971455b1a180cf62684b275 (diff)
Fix sql code
Diffstat (limited to 'sql')
-rw-r--r--sql/new.sql18
1 files changed, 18 insertions, 0 deletions
diff --git a/sql/new.sql b/sql/new.sql
new file mode 100644
index 0000000..57d918b
--- /dev/null
+++ b/sql/new.sql
@@ -0,0 +1,18 @@
+WITH users AS (
+ SELECT *
+ FROM ( VALUES ('dennis'), ('caitriao') ) AS _ (user_id)
+ )
+ SELECT track_id, name, artist
+ FROM track
+ JOIN (
+ SELECT track_id
+ FROM user_track
+ JOIN suser USING (user_id)
+ JOIN track USING (track_id)
+ JOIN lobby USING (lobby_id)
+ WHERE suser.user_name IN (SELECT user_name FROM users) AND token = 'shnPcxQ7REwF3QL1T65mcqPtf3k='
+ GROUP BY track_id
+ HAVING COUNT(track_id) = (SELECT COUNT(*) FROM users)
+ ORDER BY SUM(score) DESC
+ ) AS _ USING (track_id)
+ ;