Changes between Initial Version and Version 1 of AdvancedReports

Timestamp:

01/22/26 19:47:56 (8 days ago)

Author:

231136

Comment:

--

AdvancedReports

@@ +1 @@
+= Finkwave
+
+== Напредни извештаи од базата (SQL и складирани процедури)
+
+
+=== 1. Детален извештај за број на лајкови, слушања и просечна оценка за секој албум
+{{{
+SET search_path TO project;
+
+WITH likeCount AS (
+    SELECT
+        a.id AS album_id,
+        COUNT(l.listener_id) AS total_likes
+    FROM albums a
+    LEFT JOIN likes l ON l.musical_entity_id=a.id
+    GROUP BY a.id
+),
+listenCount AS (
+    SELECT
+        s.album_id,
+        COUNT(timestamp) AS total_listens
+    FROM listens l
+    JOIN songs s ON l.song_id = s.id
+    JOIN albums a ON a.id = s.album_id
+    WHERE album_id IS NOT NULL
+    GROUP BY s.album_id
+ ),
+averageGrade AS (
+    SELECT
+        a.id AS album_id,
+        ROUND(AVG(grade),2) AS average_grade
+    FROM reviews r
+    JOIN albums a ON a.id = r.musical_entity_id
+    GROUP BY a.id
+)
+SELECT
+    me.title,
+    COALESCE(total_likes, 0) AS total_likes,
+    COALESCE(total_listens, 0) AS total_listens,
+    COALESCE(CAST(ag.average_grade AS varchar), 'no reviews') AS average_grade
+FROM musical_entities me
+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
+ORDER BY total_likes DESC, total_listens DESC
+}}}
+
+
+=== 2. Детален извештај за број на лајкови, број на слушања, просечна оценка за една песна, како и бројот на плејлисти кој ја содржат таа 
+{{{
+SET search_path TO project;
+
+WITH likeCount AS (
+    SELECT
+        s.id AS song_id,
+        COUNT(l.listener_id) AS total_likes
+    FROM songs s
+    JOIN likes l ON l.musical_entity_id=s.id
+    GROUP BY s.id
+),
+listenCount AS (
+    SELECT
+        l.song_id AS song_id,
+        COUNT(timestamp) AS total_listens
+    FROM listens l
+    GROUP BY l.song_id
+),
+averageGrade AS (
+    SELECT
+        r.musical_entity_id AS song_id,
+        ROUND(AVG(r.grade),2) AS average_grade
+    FROM reviews r
+    JOIN songs s ON s.id = r.musical_entity_id
+    GROUP BY musical_entity_id
+),
+playlist_count AS (
+    SELECT
+        ps.song_id,
+        COUNT(ps.song_id) AS count_playlists
+    FROM songs s
+    JOIN playlist_songs ps ON s.id = ps.song_id
+    GROUP BY ps.song_id
+)
+SELECT 
+    me.title,
+    COALESCE(total_listens, 0) AS total_listens,
+    COALESCE(total_likes, 0) AS total_likes,
+    COALESCE(CAST(average_grade AS varchar), 'no reviews') AS average_grade,
+    COALESCE(count_playlists, 0) AS num_playlists
+FROM musical_entities me
+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
+ORDER BY total_likes DESC, total_listens DESC;
+}}}
+
+
+=== 3. Детален извештај за најпопуларен артист за секој настан според бројот на следбеници, а потоа вкупно слушања за секоја песна на артистот 
+{{{
+SET search_path TO 'project';
+
+WITH followersCount AS (
+    SELECT a.user_id AS artist,
+        pa.event_id,
+        COALESCE(COUNT(f.follower),0) AS followers
+    FROM artists a
+    JOIN performs_at pa ON pa.artist_id=a.user_id
+    LEFT JOIN follows f ON f.followee=a.user_id
+    GROUP BY a.user_id,pa.event_id
+),
+listensCount AS (
+    SELECT
+        a.user_id AS artist,
+        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 listens l ON l.song_id=me.id
+    GROUP BY a.user_id
+)
+SELECT
+    e.location,
+    e.venue,
+    u.full_name,
+    fc.followers,
+    lc.listen_count
+    FROM events e
+    JOIN performs_at pa ON pa.event_id=e.event_id
+    JOIN followersCount fc ON fc.artist=pa.artist_id
+    JOIN listensCount lc ON lc.artist=fc.artist
+    JOIN users u ON u.user_id=lc.artist
+    WHERE not exists(
+        SELECT 1
+        FROM artists a
+        JOIN followersCount fc1 ON a.user_id=fc1.artist
+        JOIN listensCount lc1 ON lc1.artist=fc1.artist
+        WHERE fc.event_id=fc1.event_id 
+            AND (fc.followers<fc1.followers
+                OR (fc.followers=fc1.followers AND lc.listen_count<lc1.listen_count)
+                OR (fc.followers=fc1.followers AND lc.listen_count=lc1.listen_count AND lc.artist<lc1.artist)
+                )
+);
+}}}