summaryrefslogtreecommitdiff
path: root/sql/new.sql
blob: 57d918b92d38b629ad165da5a4bbb17bc3f25ab5 (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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)
			;