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)
                )
);