Changes between Version 15 and Version 16 of AdvancedReports

Timestamp:

08/05/26 12:02:40 (20 hours ago)

Author:

231136

Comment:

--

AdvancedReports

@@ -37 +37 @@
 SELECT
     me.title,
-    COALESCE(total_likes, 0) AS total_likes,
-    COALESCE(total_listens, 0) AS total_listens,
+    COALESCE(lk.total_likes, 0) AS total_likes,
+    COALESCE(lc.total_listens, 0) AS total_listens,
     COALESCE(CAST(ag.average_grade AS varchar), 'no reviews') AS average_grade
-FROM musical_entities me
+FROM albums al
+JOIN musical_entities me ON me.id = al.id
 LEFT JOIN likeCount lk ON lk.album_id = me.id
-LEFT JOIN listenCount lc ON lc.album_id = lk.album_id
-LEFT JOIN averageGrade ag ON ag.album_id = lk.album_id
+LEFT JOIN listenCount lc ON lc.album_id = me.id
+LEFT JOIN averageGrade ag ON ag.album_id = me.id
 ORDER BY total_likes DESC, total_listens DESC;
 }}}
@@ -76 +77 @@
 Result <-
 π me.title, lk.total_likes, lc.total_listens, ag.average_grade
+(
   (
     (
       (
-        musical_entities me ⟕ (lk.album_id = me.id) LikeCount lk
+        albums al ⨝ (al.id = me.id) musical_entities me
       )
-      ⟕ ListenCount lc
-    )
-    ⟕ AverageGrade ag
-  )
+      ⟕ (me.id = lk.album_id) LikeCount lk
+    )
+    ⟕ (me.id = lc.album_id) ListenCount lc
+  )
+  ⟕ (me.id = ag.album_id) AverageGrade ag
 )
 }}}
@@ -134 +137 @@
 JOIN songs s ON me.id = s.id
 LEFT JOIN likeCount lc ON lc.song_id = s.id
-LEFT JOIN listenCount ls ON s.id= ls.song_id
-LEFT JOIN playlist_count pc ON pc.song_id = ls.song_id
-LEFT JOIN averageGrade ag ON ag.song_id = lc.song_id
+LEFT JOIN listenCount ls ON ls.song_id = s.id
+LEFT JOIN playlist_count pc ON pc.song_id = s.id
+LEFT JOIN averageGrade ag ON ag.song_id = s.id
 ORDER BY total_likes DESC, total_listens DESC;
 }}}
@@ -189 +192 @@
       ⟕ (s.id = ls.song_id) ListenCount ls
     )
-    ⟕ (ls.song_id = pc.song_id) PlaylistCount pc
-  )
-  ⟕ (lc.song_id = ag.song_id) AverageGrade ag
+    ⟕ (s.id = pc.song_id) PlaylistCount pc
+  )
+  ⟕ (s.id = ag.song_id) AverageGrade ag
 )
 }}}
@@ -217 +220 @@
     LEFT JOIN contributions c ON c.artist_id = a.user_id
     LEFT JOIN songs s ON s.id = c.musical_entity_id
-    LEFT JOIN listens l ON l.song_id=me.id
+    LEFT JOIN listens l ON l.song_id=s.id
     GROUP BY a.user_id
 )
@@ -264 +267 @@
     ⟕ (pa.artist_id = me.released_by) songs s
   )
-  ⟕ (me.id = l.song_id) listens l
+  ⟕ (s.id = l.song_id) listens l
 )