Complex DB Reports (SQL, Stored Procedures, Relational Algebra)

1. Story Statistics

SQL

WITH likeCount AS (
    SELECT
        story_id,
        COUNT(user_id) AS total_likes
    FROM likes
    GROUP BY story_id
),
commentCount AS (
    SELECT
        story_id,
        COUNT(comment_id) AS total_comments
    FROM comment
    GROUP BY story_id
),
averageRating AS (
    SELECT
        story_id,
        ROUND(AVG(rating), 2) AS avg_rating
    FROM chapter
    WHERE rating IS NOT NULL
    GROUP BY story_id
)
SELECT
    u.username AS writer,
    s.story_id,
    s.short_description,
    st.status,
    COALESCE(lk.total_likes, 0) AS total_likes,
    COALESCE(cm.total_comments, 0)         AS total_comments,
    COALESCE(CAST(ar.avg_rating AS VARCHAR), 'no ratings')    AS avg_rating
FROM story              s
JOIN status             st ON s.story_id = st.story_id
JOIN writer             w  ON s.user_id  = w.user_id
JOIN users              u  ON w.user_id  = u.user_id
LEFT JOIN likeCount     lk ON s.story_id = lk.story_id
LEFT JOIN commentCount  cm ON s.story_id = cm.story_id
LEFT JOIN averageRating ar ON s.story_id = ar.story_id
ORDER BY total_likes DESC, total_comments DESC;

Relational Algebra

likeCount <-
γ story_id;
  total_likes := COUNT(user_id)
(
  likes
)

commentCount <-
γ story_id;
  total_comments := COUNT(comment_id)
(
  comment
)

averageRating <-
γ story_id;
  avg_rating := ROUND(AVG(rating), 2)
(
  σ rating ≠ NULL (chapter)
)

Base <-
story s
⨝ (s.story_id = st.story_id) status st
⨝ (s.user_id = w.user_id)   writer w
⨝ (w.user_id = u.user_id)   users u

WithLikes <-
Base
⟕ (s.story_id = lk.story_id) likeCount lk

WithComments <-
WithLikes
⟕ (s.story_id = cm.story_id) commentCount cm

WithRatings <-
WithComments
⟕ (s.story_id = ar.story_id) averageRating ar

Result <-
π
  u.username → writer,
  s.story_id,
  s.short_description,
  st.status,
  COALESCE(lk.total_likes, 0)             → total_likes,
  COALESCE(cm.total_comments, 0)          → total_comments,
  COALESCE(ar.avg_rating, 'no ratings')   → avg_rating
(
  WithRatings
)