Напредни извештаи

Да се излистаат сите албуми според бројот на песни, датумот на објавување и вкупното времетраење

SELECT
    A.album_id,
    A.album_title,
    A.album_date,
    COUNT(S.song_id) AS number_of_songs,
    SUM(C.creation_duration) AS total_duration
FROM
    ALBUM A
JOIN
    SONG S ON A.album_id = S.album_id
JOIN
    CREATION C ON S.creation_id = C.creation_id
GROUP BY
    A.album_id, A.album_title, A.album_date
ORDER BY
    number_of_songs DESC;

Да се излистаат сите подкасти кои што имаат повеќе од 60% позитивни реакции, притоа бројот на коментари да биде поголем од 50

SELECT
    P.podcast_id,
    P.podcast_title,
    COUNT(DISTINCT BC.comment_id) AS number_of_comments,
    COUNT(DISTINCT RW.reaction_id) AS number_of_reactions,
    (COUNT(DISTINCT RW.reaction_id) / COUNT(DISTINCT BC.comment_id)) * 100 AS positive_reaction_percentage
FROM
    PODCAST P
JOIN
    BUZZCOMMENT BC ON P.podcast_id = BC.commented_on
JOIN
    REACTS_WITH RW ON BC.comment_id = RW.user_id
WHERE
    BC.comment_rating > 0
GROUP BY
    P.podcast_id, P.podcast_title
HAVING
    (COUNT(DISTINCT RW.reaction_id) / COUNT(DISTINCT BC.comment_id)) * 100 > 60
    AND COUNT(DISTINCT BC.comment_id) > 50;

Да се групираат песните/албумите/подкастите според потеклото на мнозинството слушатели

WITH CreationListenerOrigin AS (
    SELECT
        C.creation_id,
        L.listener_id,
        MAX(L.listener_origin) AS majority_origin
    FROM
        (
            SELECT song_id AS creation_id FROM SONG
            UNION
            SELECT album_id AS creation_id FROM ALBUM
            UNION
            SELECT podcast_id AS creation_id FROM PODCAST
        ) C
    JOIN
        LISTENS_TO L ON C.creation_id = L.creation_id
    GROUP BY
        C.creation_id, L.listener_id
)

SELECT
    CLO.creation_id,
    CLO.majority_origin,
    CASE
        WHEN S.song_id IS NOT NULL THEN 'SONG'
        WHEN A.album_id IS NOT NULL THEN 'ALBUM'
        WHEN P.podcast_id IS NOT NULL THEN 'PODCAST'
    END AS creation_type
FROM
    CreationListenerOrigin CLO
LEFT JOIN
    SONG S ON CLO.creation_id = S.song_id
LEFT JOIN
    ALBUM A ON CLO.creation_id = A.album_id
LEFT JOIN
    PODCAST P ON CLO.creation_id = P.podcast_id
ORDER BY
    CLO.creation_id;

Да се најдат жанровите кои најмногу се слушани во определен период од годината

SELECT
    G.genre_id,
    G.genre_name,
    COUNT(*) AS listen_count
FROM
    LISTENS_TO L
JOIN
    SONG S ON L.creation_id = S.song_id
JOIN
    GENRE G ON S.genre_id = G.genre_id
WHERE
    EXTRACT(MONTH FROM L.listens_timestamp) = <месец> AND EXTRACT(YEAR FROM L.listens_timestamp) = <година>
GROUP BY
    G.genre_id, G.genre_name
ORDER BY
    listen_count DESC;

Прикажи ги сите песни кои имаат просечен рејтинг поголем од 4.5 и се класификуваат како 'поп' или 'рок', а имаат најмалку 100000 преслушувања.

SELECT song_name, AVG(rating) AS average_rating, COUNT(streams) AS number_of_streams
FROM songs
JOIN ratings ON songs.song_id = ratings.song_id
JOIN classifications ON songs.song_id = classifications.song_id
WHERE classifications.genre IN ('pop', 'rock')
GROUP BY song_name
HAVING average_rating > 4.5 AND number_of_streams > 100000;

Подкасти кои имаат најмалку 50 епизоди и се класификуваат како образовни

SELECT p.podcast_title, COUNT(e.episode_id) AS total_episodes
FROM PODCAST p
JOIN EPISODE e ON p.podcast_id = e.podcast_id
JOIN CATEGORY c ON p.category_id = c.category_id
WHERE c.category_name = 'Education'
GROUP BY p.podcast_id, p.podcast_title
HAVING COUNT(e.episode_id) > 50;

Концерти кои ќе се одржат во Лондон и ќе вклучуваат информации за изведувачите, времетраењето на настанот и бројот на преслушувања на нивните песни:

SELECT 
    c.concert_name, 
    c.concert_location, 
    c.concert_timeDate, 
    COUNT(l.song_id) AS total_listens,
    a.information AS artist_information
FROM CONCERT c
JOIN BUZZEVENT b ON c.event_id = b.event_id
JOIN ARTIST a ON b.user_id = a.user_id
JOIN SONG s ON a.user_id = s.created_by
JOIN LISTENS l ON s.song_id = l.song_id
WHERE c.concert_location = 'London'
GROUP BY c.concert_name, c.concert_location, c.concert_timeDate, a.information
ORDER BY c.concert_timeDate;

Да се прикажат информациите за изведувачите, вклучувајќи ги нивните имиња, бројот на песни кои ги креирале и просечниот рејтинг на нивните песни:

SELECT 
    a.user_id,
    a.information AS artist_name,
    COUNT(s.song_id) AS total_songs,
    AVG(r.rating) AS average_rating
FROM ARTIST a
LEFT JOIN SONG s ON a.user_id = s.created_by
LEFT JOIN RATINGS r ON s.song_id = r.song_id
GROUP BY a.user_id, a.information
ORDER BY average_rating DESC;

Да се излистаат најдодаваните песни според регионот на корисниците,и да ги редоследува првите 10 најдодавани песни за секој регион. Песните според бројот на плејлисти во кои се наоѓаат, во опаѓачки редослед.

SELECT s.song_title, COUNT(*) AS total_playlists, u.user_region
FROM SONG s
JOIN PLAYLIST_SONG ps ON s.song_id = ps.song_id
JOIN PLAYLIST p ON ps.playlist_id = p.playlist_id
JOIN LISTENER l ON p.created_by = l.user_id
JOIN USER u ON l.user_id = u.user_id
GROUP BY s.song_id, s.song_title, u.user_region
ORDER BY u.user_region, total_playlists DESC
LIMIT 10;