Changes between Version 2 and Version 3 of AdvancedReports

Timestamp:

01/23/26 19:24:49 (7 days ago)

Author:

231136

Comment:

--

AdvancedReports

@@ -144 +144 @@
 );
 }}}
+
+
+== 4. Детален извештај за најпопуларниот артист според месечни слушања за изминатата година
+{{{
+SET search_path TO 'project';
+
+with one_year AS (
+    SELECT
+        DATE_TRUNC('year', CURRENT_DATE) - INTERVAL '1 year' AS year_start,
+        (DATE_TRUNC('year', CURRENT_DATE) - INTERVAL '1 year') + INTERVAL '1 year - 1 day' AS year_end
+),
+monthly_artist_listens AS (
+    SELECT
+        EXTRACT(MONTH FROM l.timestamp) AS month,
+        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
+    GROUP BY EXTRACT(MONTH FROM l.timestamp),a.user_id
+),
+max_per_month AS(
+    SELECT
+        mal.month AS month,
+        MAX(count) AS max_month_counter
+    FROM monthly_artist_listens mal
+    GROUP BY mal.month
+    ORDER BY mal.month
+),
+most_popular AS (
+    SELECT
+        mal.month,
+        user_id,
+        max_month_counter
+    FROM monthly_artist_listens mal
+    JOIN max_per_month mpm ON mpm.month=mal.month AND mpm.max_month_counter=mal.count
+    WHERE NOT EXISTS (
+        SELECT 1
+        FROM monthly_artist_listens mal1
+        JOIN max_per_month mpm1 ON mpm1.month=mal1.month AND mpm1.max_month_counter=mal1.count
+        WHERE mal.user_id > mal1.user_id AND mal1.month=mal.month
+        )
+    ORDER BY mal.month
+),
+months AS (
+    SELECT
+        n AS month_num,
+        TO_CHAR(MAKE_DATE(2025, n, 1), 'Month') AS month_name
+    FROM GENERATE_SERIES(1, 12) AS g(n)
+)
+SELECT 
+    CONCAT(EXTRACT(YEAR FROM oy.year_start),'-',m.month_name) AS month,
+    COALESCE(u.full_name,'n/a') AS artist_name,
+    COALESCE(mp.max_month_counter,0) AS aonthly_listens
+FROM months m
+LEFT JOIN most_popular mp ON mp.month=m.month_num
+LEFT JOIN users u ON mp.user_id=u.user_id
+CROSS JOIN one_year oy
+ORDER BY m.month_num
+}}}