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

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 monthly_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

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