Finkwave

Напредни извештаи од базата (SQL и складирани процедури)

1. Детален извештај за број на лајкови, слушања и просечна оценка за секој албум

SQL

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;

Релациона Алгебра

LikeCount <- γ album_id := a.id;
  total_likes := COUNT(l.listener_id)
(
  albums a ⟕ (a.id = l.musical_entity_id) likes l
)

ListenCount <-
γ album_id := s.album_id;
  total_listens := COUNT(l.timestamp)
(
  σ s.album_id IS NOT NULL
  (
    (listens l ⨝ (l.song_id = s.id) songs s)
      ⨝ (s.album_id = a.id) albums a
  )
)

AverageGrade <-
γ album_id := a.id;
  average_grade := AVG(r.grade)
(
  reviews r ⨝ (r.musical_entity_id = a.id) albums a
)

Result <-
π me.title, lk.total_likes, lc.total_listens, ag.average_grade
  (
    (
      (
        musical_entities me ⟕ (lk.album_id = me.id) LikeCount lk
      )
      ⟕ ListenCount lc
    )
    ⟕ AverageGrade ag
  )
)

2. Детален извештај за број на лајкови, број на слушања, просечна оценка за една песна, како и бројот на плејлисти кои ја содржат

SQL

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;

Релациона Алгебра

LikeCount <-
γ song_id := s.id;
  total_likes := COUNT(l.listener_id)
(
  songs s ⨝ (s.id = l.musical_entity_id) likes l
)

ListenCount <-
γ song_id := l.song_id;
  total_listens := COUNT(l.timestamp)
(
  listens l
)

AverageGrade <-
γ song_id := r.musical_entity_id;
  average_grade := AVG(r.grade)
(
  reviews r ⨝ (r.musical_entity_id = s.id) songs s
)

PlaylistCount <-
γ song_id := ps.song_id;
  count_playlists := COUNT(ps.song_id)
(
  songs s ⨝ (s.id = ps.song_id) playlist_songs ps
)

Result <-
π me.title,
  ls.total_listens,
  lc.total_likes,
  ag.average_grade,
  pc.count_playlists
(
  (
    (
      (
        (
          musical_entities me
          ⨝ (me.id = s.id) songs s
        )
        ⟕ (s.id = lc.song_id) LikeCount lc
      )
      ⟕ (s.id = ls.song_id) ListenCount ls
    )
    ⟕ (ls.song_id = pc.song_id) PlaylistCount pc
  )
  ⟕ (lc.song_id = ag.song_id) AverageGrade ag
)

3. Детален извештај за најпопуларен артист за секој настан рангиран по бројот на следбеници, а потоа по вкупно слушања на сите негови песни

SQL

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

Релациона Алгебра

FollowersCount <-
γ artist := a.user_id,
  event_id := pa.event_id;
  followers := COUNT(f.follower)
(
  (artists a ⨝ (a.user_id = pa.artist_id) performs_at pa)
  ⟕ (a.user_id = f.followee) follows f
)

ListensCount <-
γ artist := a.user_id;
  listen_count := COUNT(l.timestamp)
(
  (
    (artists a ⨝ (a.user_id = pa.artist_id) performs_at pa)
    ⨝ (pa.artist_id = me.released_by) musical_entities me
  )
  ⟕ (me.id = l.song_id) listens l
)

BaseResult <-
(
  (
    (
      (
        events e
        ⨝ (e.event_id = pa.event_id) performs_at pa
      )
      ⨝ (pa.artist_id = fc.artist) FollowersCount fc
    )
    ⨝ (fc.artist = lc.artist) ListensCount lc
  )
  ⨝ (lc.artist = u.user_id) users u
)

ToRemove <-
σ
  fc.event_id = fc1.event_id ∧
  (
    fc.followers < fc1.followers
    ∨ (fc.followers = fc1.followers ∧ lc.listen_count < lc1.listen_count)
    ∨ (fc.followers = fc1.followers ∧ lc.listen_count = lc1.listen_count ∧ lc.artist < lc1.artist)
  )
(
  (FollowersCount fc ⨝ ListensCount lc)
  ×
  (ρ fc1(FollowersCount) ⨝ ρ lc1(ListensCount))
)

FinalArtists <-
BaseResult − π attributes(BaseResult)
(
  BaseResult ⨝ (BaseResult.artist = ToRemove.artist) ToRemove
)

Result <-
π e.location,
  e.venue,
  u.full_name,
  fc.followers,
  lc.listen_count
(
  FinalArtists
)

4. Детален извештај за најпопуларниот артист според месечни слушања за изминатата година

SQL

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;

Релациона Алгебра

OneYear <-
{ (year_start, year_end) }

MonthlyArtistListens <-
γ month := MONTH(l.timestamp),
  user_id := a.user_id;
  count := COUNT(l.timestamp)
(
  (
    (
      listens l
      ⨝ (l.timestamp ≥ oy.year_start ∧ l.timestamp ≤ oy.year_end) OneYear oy
    )
    ⨝ (l.song_id = me.id) musical_entities me
  )
  ⨝ (me.released_by = a.user_id) artists a
)

MaxPerMonth =
γ month := mal.month;
  max_month_counter := MAX(mal.count)
(
  MonthlyArtistListens mal
)

MaxCandidates <-
MonthlyArtistListens mal
⨝ (mal.month = mpm.month ∧ mal.count = mpm.max_month_counter)
MaxPerMonth mpm

ToRemove <-
σ
  mal.month = mal1.month ∧
  mal.user_id > mal1.user_id
(
  ρ mal(MaxCandidates)
  ×
  ρ mal1(MaxCandidates)
)

MostPopular <-
MaxCandidates − π attributes(MaxCandidates)
(
  MaxCandidates ⨝ (user_id) ToRemove
)

Months(month_num, month_name)

FinalResult <-
π
  CONCAT(YEAR(oy.year_start), '-', m.month_name),
  u.full_name,
  mp.max_month_counter
(
  (
    (
      Months m
      ⟕ (m.month_num = mp.month) MostPopular mp
    )
    ⟕ (mp.user_id = u.user_id) users u
  )
  × OneYear oy
)

5. Детален извештај за процентуалната промена на бројот на слушања на артистите за изминатите 30 дена

SQL

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;

Релациона Алгебра

OneMonth <-
{ (prev_period_start,
   prev_period_end,
   this_period_start,
   this_period_end) }
   
LastPeriod <-
σ last_period_count > 0
(
  γ user_id := a.user_id;
    last_period_count := COUNT(l.timestamp)
  (
    (
      (
        listens l ⨝ (l.timestamp ≥ om.prev_period_start ∧
           l.timestamp ≤ om.prev_period_end)
        OneMonth om
      )
      ⨝ (l.song_id = me.id) musical_entities me
    )
    ⟖ (me.released_by = a.user_id) artists a
  )
)

ThisPeriod <-
γ user_id := a.user_id;
  this_period_count := COUNT(l.timestamp)
(
  (
    (
      listens l ⨝ (l.timestamp ≥ om.this_period_start ∧
         l.timestamp ≤ om.this_period_end)
      OneMonth om
    )
    ⨝ (l.song_id = me.id) musical_entities me
  )
  ⟖ (me.released_by = a.user_id) artists a
)

Stats <-
π
  u.full_name,
  lp.last_period_count,
  tp.this_period_count,
  om.prev_period_start,
  om.prev_period_end,
  om.this_period_start,
  om.this_period_end,
  pct_change := ((tp.this_period_count − lp.last_period_count)
                 / lp.last_period_count) * 100
(
  (
    (
      LastPeriod lp ⟕ ThisPeriod tp
    )
    ⨝ users u
  )
  × OneMonth om
)

Result <-
τ pct_change DESC
(
  π
    full_name,
    previous_period,
    last_period_count,
    this_period,
    this_period_count,
    pct_change
  (
    Stats s
  )
)