Changes between Version 4 and Version 5 of AdvancedReports

Timestamp:

01/23/26 20:19:41 (7 days ago)

Author:

231136

Comment:

--

AdvancedReports

@@ -146 +146 @@
 
 
-== 4. Детален извештај за најпопуларниот артист според месечни слушања за изминатата година
+=== 4. Детален извештај за најпопуларниот артист според месечни слушања за изминатата година
 {{{
 SET search_path TO 'project';
@@ -204 +204 @@
 ORDER BY m.month_num
 }}}
+
+
+=== 5. Детален извештај за процентуалната промена на бројот на слушања на артистите за изминатите 30 дена
+
+{{{
+SET search_path TO 'project';
+
+WITH one_month AS (
+    SELECT
+        CURRENT_DATE - INTERVAL '60 days' AS prev_period_start,
+        CURRENT_DATE - INTERVAL '30 days' AS prev_period_end,
+        CURRENT_DATE - INTERVAL '29 days' AS this_period_start,
+        CURRENT_DATE AS this_period_end
+),
+last_period AS (
+    SELECT
+        a.user_id,
+        count(l.timestamp) AS last_period_count
+    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
+    GROUP BY a.user_id
+    HAVING count(l.timestamp) > 0
+),
+this_period AS (
+    SELECT
+        a.user_id,
+        count(l.timestamp) AS this_period_count
+    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
+    GROUP BY a.user_id
+),
+stats AS (
+    SELECT
+        u.full_name,
+        CONCAT(om.prev_period_start::DATE, ' | ',om.prev_period_end::DATE) AS previous_period,
+        last_period_count,
+        CONCAT(om.this_period_start::DATE, ' | ',om.this_period_end::DATE) AS this_period,
+        this_period_count,
+        ROUND(((CAST(this_period_count AS DOUBLE PRECISION) - last_period_count) / NULLIF(last_period_count, 0)) * 100) AS pct_change
+    FROM last_period lp
+    LEFT JOIN this_period tp ON lp.user_id=tp.user_id
+    JOIN users u ON lp.user_id = u.user_id
+    CROSS JOIN one_month om
+)
+SELECT
+    s.full_name,
+    s.previous_period,
+    s.last_period_count,
+    s.this_period,
+    s.this_period_count,
+    CONCAT(s.pct_change, '%')
+FROM stats s
+ORDER BY pct_change DESC
+
+}}}