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
)

2. Validate and insert story into user reading list with confirmation

WITH
published_story AS (
    SELECT s.story_id, s.short_description
    FROM story s
    JOIN status st ON s.story_id = st.story_id
    WHERE s.story_id = 1
      AND st.status = 'published'
),
user_list AS (
    SELECT list_id, list_name, is_public
    FROM reading_list
    WHERE list_id = 1
      AND user_id = 4
),
already_added AS (
    SELECT 1
    FROM reading_list_items
    WHERE list_id = 1
      AND story_id = 1
),
inserted AS (
    INSERT INTO reading_list_items (list_id, story_id, added_at)
    SELECT ul.list_id, ps.story_id, CURRENT_TIMESTAMP
    FROM user_list ul
    CROSS JOIN published_story ps
    WHERE NOT EXISTS (SELECT 1 FROM already_added)
    RETURNING list_id, story_id, added_at
)
SELECT
    rl.list_id,
    rl.list_name,
    rl.is_public,
    u.username                  AS owner,
    COUNT(rli.story_id)         AS total_items,
    MAX(rli.added_at)           AS last_added_at,
    CASE
        WHEN ins.story_id IS NOT NULL         THEN 'Story successfully added'
        WHEN EXISTS (SELECT 1 FROM already_added) THEN 'Story already in list'
        ELSE                                       'Failed: story not published or list not found'
    END                         AS result_message
FROM reading_list       rl
JOIN users              u   ON rl.user_id  = u.user_id
LEFT JOIN reading_list_items rli ON rl.list_id = rli.list_id
LEFT JOIN inserted      ins ON ins.list_id  = rl.list_id
WHERE rl.list_id = 1
GROUP BY rl.list_id, rl.list_name, rl.is_public, u.username, ins.story_id;

Relational Algebra

PublishedStory <-
π story_id, short_description
(
  σ s.story_id = :story_id ∧ st.status = 'published'
  (
    story s
    ⨝ (s.story_id = st.story_id) status st
  )
)

UserList <-
σ list_id = :list_id ∧ user_id = :user_id
(
  reading_list
)

AlreadyAdded <-
σ list_id = :list_id ∧ story_id = :story_id
(
  reading_list_items
)

NewItem <-
{ (list_id := :list_id, story_id := :story_id, added_at := CURRENT_TIMESTAMP) }

ToInsert <-
(UserList ⨯ PublishedStory)
− π list_id, story_id, added_at (AlreadyAdded)

reading_list_items <- reading_list_items ∪ ToInsert

UpdatedCount <-
γ list_id;
  total_items  := COUNT(story_id),
  last_added   := MAX(added_at)
(
  σ list_id = :list_id (reading_list_items)
)

Result <-
π
  rl.list_id,
  rl.list_name,
  rl.is_public,
  u.username                                        → owner,
  uc.total_items,
  uc.last_added,
  CASE
    WHEN ins.story_id IS NOT NULL     → 'Story successfully added'
    WHEN AlreadyAdded ≠ ∅             → 'Story already in list'
    ELSE                               'Failed: story not published or list not found'
  END                                               → result_message
(
  (
    reading_list rl
    ⨝ (rl.user_id = u.user_id) users u
  )
  ⨝  (rl.list_id = uc.list_id) UpdatedCount uc
  ⟕  (rl.list_id = ins.list_id) ToInsert ins
)