blob: 0b2cb2249d9c8cd88a4019f1e717d41cff9901d7 (
plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
|
/*
SELECT track_id, SUM(score)/2
FROM user_track
WHERE user_id IN (
SELECT user_id
FROM suser
WHERE user_name = 'd-kobert'
OR user_name = 'thilo'
)
GROUP BY track_id
HAVING COUNT(track_id) = 2
ORDER BY SUM(score) DESC;
*/
-- alternative
WITH users AS (
SELECT *
FROM ( VALUES
('d-kobert'), ('thilo')
) AS _ (user_name)
)
--SELECT track_id, SUM(score) / (SELECT COUNT(*) FROM users) AS score
SELECT track_id, name, artist
FROM track
JOIN (
SELECT track_id
FROM user_track
JOIN suser USING (user_id)
JOIN track USING (track_id)
WHERE suser.user_name IN (SELECT * FROM users)
GROUP BY track_id
HAVING COUNT(track_id) = (SELECT COUNT(*) FROM users)
ORDER BY SUM(score) DESC
) AS _ USING (track_id)
;
|
