summaryrefslogtreecommitdiff
path: root/sql
diff options
context:
space:
mode:
authorDennis Kobert <dennis@kobert.dev>2020-03-02 16:21:26 +0000
committerDennis Kobert <dennis@kobert.dev>2020-03-02 16:21:26 +0000
commit1a95bd4e610770bed1571de6ddf572d5590561d9 (patch)
treebfd5b41bf0ee9ec9b49a2b31e212185ddb888aaa /sql
parent17a305d420c4fd88fcaabb479164e6095303f7fc (diff)
Rework table structure
add sql queries
Diffstat (limited to 'sql')
-rw-r--r--sql/si.sql37
-rw-r--r--sql/weighted.sql8
2 files changed, 45 insertions, 0 deletions
diff --git a/sql/si.sql b/sql/si.sql
new file mode 100644
index 0000000..0b2cb22
--- /dev/null
+++ b/sql/si.sql
@@ -0,0 +1,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)
+;
diff --git a/sql/weighted.sql b/sql/weighted.sql
new file mode 100644
index 0000000..dfa23c1
--- /dev/null
+++ b/sql/weighted.sql
@@ -0,0 +1,8 @@
+CREATE OR REPLACE VIEW user_track_weighted AS (
+ SELECT *, "count" / (
+ SELECT SUM("count")
+ FROM user_track ut
+ WHERE ut.user_id = user_id
+ )::decimal AS score
+ FROM user_track
+);