Changes between Version 13 and Version 14 of AdvancedReports

Timestamp:

08/05/26 11:02:56 (21 hours ago)

Author:

231136

Comment:

--

AdvancedReports

@@ -215 +215 @@
         COALESCE(COUNT(l.timestamp),0) AS listen_count
     FROM artists a
-    JOIN performs_at pa ON pa.artist_id=a.user_id
-    JOIN musical_entities me ON me.released_by=pa.artist_id
+    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
     GROUP BY a.user_id
@@ -228 +228 @@
     FROM events e
     JOIN performs_at pa ON pa.event_id=e.event_id
-    JOIN followersCount fc ON fc.user_id=pa.artist_id
+    JOIN followersCount fc ON fc.user_id = pa.artist_id AND fc.event_id = e.event_id
     JOIN listensCount lc ON lc.user_id=fc.user_id
     JOIN users u ON u.user_id=lc.user_id
@@ -262 +262 @@
 (
   (
-    (artists a ⨝ (a.user_id = pa.artist_id) performs_at pa)
-    ⨝ (pa.artist_id = me.released_by) musical_entities me
+    (artists a ⟕ (a.user_id = c.artist_id) contributions c)
+    ⟕ (pa.artist_id = me.released_by) songs s
   )
   ⟕ (me.id = l.song_id) listens l
@@ -276 +276 @@
         ⨝ (e.event_id = pa.event_id) performs_at pa
       )
-      ⨝ (pa.artist_id = fc.user_id) FollowersCount fc
-    )
-    ⨝ ListensCount lc
-  )
-  ⨝ users u
+      ⨝ (pa.artist_id = fc.user_id ∧ e.event_id = fc.event_id) FollowersCount fc
+    )
+    ⨝ (fc.user_id = lc.user_id) ListensCount lc
+  )
+  ⨝ (lc.user_id = u.user_id) users u
 )
 
@@ -328 +328 @@
     SELECT
         EXTRACT(MONTH FROM l.timestamp) AS month,
-        a.user_id,COUNT(l.timestamp)AS count
+        a.user_id,
+        COUNT(l.timestamp)AS count
     FROM listens l
     JOIN one_year oy ON l.timestamp>=oy.year_start AND l.timestamp<=oy.year_end
-    JOIN musical_entities me ON l.song_id=me.id
-    JOIN artists a ON a.user_id=me.released_by
+    JOIN contributions c ON c.musical_entity_id = l.song_id
+    JOIN artists a ON a.user_id=c.artist_id
     GROUP BY EXTRACT(MONTH FROM l.timestamp),a.user_id
 ),
@@ -391 +392 @@
       ⨝ (l.timestamp ≥ oy.year_start ∧ l.timestamp ≤ oy.year_end) OneYear oy
     )
-    ⨝ (l.song_id = me.id) musical_entities me
-  )
-  ⨝ (me.released_by = a.user_id) artists a
+    ⨝ (l.song_id = c.musical_entity_id) contributions c
+  )
+  ⨝ (c.artist_id = a.user_id) artists a
 )
 
@@ -462 +463 @@
     FROM listens l
     JOIN one_month om ON l.timestamp between om.prev_period_start AND om.prev_period_end
-    JOIN musical_entities me ON l.song_id = me.id
-    RIGHT JOIN artists a ON a.user_id = me.released_by
+    JOIN contributions c ON c.musical_entity_id = l.song_id
+    RIGHT JOIN artists a ON a.user_id = c.artist_id
     GROUP BY a.user_id
     HAVING count(l.timestamp) > 0
@@ -473 +474 @@
     FROM listens l
     JOIN one_month om ON l.timestamp between om.this_period_start AND om.this_period_end
-    JOIN musical_entities me ON l.song_id = me.id
-    RIGHT JOIN artists a ON a.user_id = me.released_by
+    JOIN contributions c ON c.musical_entity_id = l.song_id
+    RIGHT JOIN artists a ON a.user_id = c.artist_id
     GROUP BY a.user_id
 ),
@@ -524 +525 @@
         OneMonth om
       )
-      ⨝ (l.song_id = me.id) musical_entities me
-    )
-    ⟖ (me.released_by = a.user_id) artists a
+      ⨝ (l.song_id = c.musical_entity_id) contributions c
+    )
+    ⟖ (c.artist_id = a.user_id) artists a
   )
 )
@@ -540 +541 @@
       OneMonth om
     )
-    ⨝ (l.song_id = me.id) musical_entities me
-  )
-  ⟖ (me.released_by = a.user_id) artists a
+    ⨝ (l.song_id = c.musical_entity_id) contributions c
+  )
+  ⟖ (c.artist_id = a.user_id) artists a
 )