= 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. Annual genre popularity and engagement trend
==== SQL
{{{ 
WITH genre_annual AS (
    SELECT
        DATE_TRUNC('year', s.story_created_at) AS year,
        g.genre_id,
        g.genre_name,
        COUNT(DISTINCT s.story_id) AS total_stories,
        COUNT(DISTINCT w.user_id) AS total_writers,
        COALESCE(SUM(ch.view_count), 0) AS total_views,
        COALESCE(SUM(ch.word_count), 0) AS total_words,
        COUNT(DISTINCT l.user_id) AS total_likes,
        COUNT(DISTINCT c.comment_id) AS total_comments,
        ROUND(AVG(ch.rating), 2) AS avg_rating
    FROM genre               g
    JOIN has_genre           hg ON g.genre_id     = hg.genre_id
    JOIN story               s  ON hg.story_id    = s.story_id
    JOIN writer              w  ON s.user_id       = w.user_id
    JOIN status              st ON s.story_id      = st.story_id
                                AND st.status      = 'published'
    LEFT JOIN chapter        ch ON s.story_id      = ch.story_id
    LEFT JOIN likes          l  ON s.story_id      = l.story_id
    LEFT JOIN comment        c  ON s.story_id      = c.story_id
    GROUP BY
        DATE_TRUNC('year', s.story_created_at),
        g.genre_id, g.genre_name
),
with_metrics AS (
    SELECT
        *,
        ROUND(
            (total_likes + total_comments)::DECIMAL
            / NULLIF(total_views, 0) * 100, 2
        ) AS engagement_rate,
        ROUND(
            total_views::DECIMAL
            / NULLIF(total_stories, 0), 2
        ) AS avg_views_per_story,
        LAG(total_views) OVER (
            PARTITION BY genre_id ORDER BY year
        ) AS prev_year_views,
        LAG(total_stories) OVER (
            PARTITION BY genre_id ORDER BY year
        ) AS prev_year_stories
    FROM genre_annual
)
SELECT
    TO_CHAR(year, 'YYYY') AS year,
    genre_name,
    total_stories,
    total_writers,
    total_views,
    avg_views_per_story,
    total_likes,
    total_comments,
    COALESCE(avg_rating, 0) AS avg_rating,
    COALESCE(engagement_rate, 0) AS engagement_rate,
    ROUND(
        (total_views - prev_year_views)::DECIMAL
        / NULLIF(prev_year_views, 0) * 100, 2
    ) AS yoy_views_growth_pct,
    ROUND(
        (total_stories - prev_year_stories)::DECIMAL
        / NULLIF(prev_year_stories, 0) * 100, 2
    ) AS yoy_stories_growth_pct,
    RANK() OVER (
        PARTITION BY year
        ORDER BY total_views DESC
    ) AS popularity_rank,
    RANK() OVER (
        PARTITION BY year
        ORDER BY engagement_rate DESC
    ) AS engagement_rank
FROM with_metrics
ORDER BY year DESC, popularity_rank;
}}}

=== Relational Algebra
{{{
PublishedStories ←
σ st.status = 'published'
(
  genre g
  ⨝ (g.genre_id = hg.genre_id)  has_genre hg
  ⨝ (hg.story_id = s.story_id)  story s
  ⨝ (s.user_id = w.user_id)     writer w
  ⨝ (s.story_id = st.story_id)  status st
)

WithChapters ←
PublishedStories
⟕ (s.story_id = ch.story_id) chapter ch

WithLikes ←
WithChapters
⟕ (s.story_id = l.story_id) likes l

WithComments ←
WithLikes
⟕ (s.story_id = c.story_id) comment c

GenreAnnual ←
γ
  year       := DATE_TRUNC('year', s.story_created_at),
  genre_id   := g.genre_id,
  genre_name := g.genre_name;
  total_stories  := COUNT(DISTINCT s.story_id),
  total_writers  := COUNT(DISTINCT w.user_id),
  total_views    := COALESCE(SUM(ch.view_count), 0),
  total_words    := COALESCE(SUM(ch.word_count), 0),
  total_likes    := COUNT(DISTINCT l.user_id),
  total_comments := COUNT(DISTINCT c.comment_id),
  avg_rating     := ROUND(AVG(ch.rating), 2)
(
  WithComments
)

WithMetrics ←
π
  year,
  genre_id,
  genre_name,
  total_stories,
  total_writers,
  total_views,
  total_words,
  total_likes,
  total_comments,
  avg_rating,
  ROUND((total_likes + total_comments) / NULLIF(total_views, 0) * 100, 2)
                                          → engagement_rate,
  ROUND(total_views / NULLIF(total_stories, 0), 2)
                                          → avg_views_per_story,
  LAG(total_views)   OVER (PARTITION BY genre_id ORDER BY year)
                                          → prev_year_views,
  LAG(total_stories) OVER (PARTITION BY genre_id ORDER BY year)
                                          → prev_year_stories
(
  GenreAnnual
)

Result ←
π
  TO_CHAR(year, 'YYYY')                   → year,
  genre_name,
  total_stories,
  total_writers,
  total_views,
  avg_views_per_story,
  total_likes,
  total_comments,
  COALESCE(avg_rating, 0)                 → avg_rating,
  COALESCE(engagement_rate, 0)            → engagement_rate,
  ROUND(
    (total_views - prev_year_views)
    / NULLIF(prev_year_views, 0) * 100, 2
  )                                       → yoy_views_growth_pct,
  ROUND(
    (total_stories - prev_year_stories)
    / NULLIF(prev_year_stories, 0) * 100, 2
  )                                       → yoy_stories_growth_pct,
  RANK() OVER (PARTITION BY year ORDER BY total_views DESC)
                                          → popularity_rank,
  RANK() OVER (PARTITION BY year ORDER BY engagement_rate DESC)
                                          → engagement_rank
(
  WithMetrics
)
}}}

=== 3. Quarterly writer performance report
==== SQL
{{{
WITH quarterly_stats AS (
    SELECT
        DATE_TRUNC('quarter', s.story_created_at)   AS quarter,
        u.user_id,
        u.username,
        u.user_name,
        u.surname,
        COUNT(DISTINCT s.story_id) AS stories_published,
        COUNT(DISTINCT ch.chapter_id) AS chapters_written,
        COALESCE(SUM(ch.view_count), 0) AS total_views,
        COALESCE(SUM(ch.word_count), 0) AS total_words,
        COUNT(DISTINCT l.user_id) AS total_likes,
        COUNT(DISTINCT c.comment_id) AS total_comments,
        ROUND(AVG(ch.rating), 2) AS avg_rating
    FROM story               s
    JOIN writer              w  ON s.user_id    = w.user_id
    JOIN users               u  ON w.user_id    = u.user_id
    JOIN status              st ON s.story_id   = st.story_id
                                AND st.status   = 'published'
    LEFT JOIN chapter        ch ON s.story_id   = ch.story_id
    LEFT JOIN likes          l  ON s.story_id   = l.story_id
    LEFT JOIN comment        c  ON s.story_id   = c.story_id
    GROUP BY
        DATE_TRUNC('quarter', s.story_created_at),
        u.user_id, u.username, u.user_name, u.surname
),
with_growth AS (
    SELECT
        *,
        LAG(total_views)    OVER (PARTITION BY user_id ORDER BY quarter) AS prev_views,
        LAG(total_likes)    OVER (PARTITION BY user_id ORDER BY quarter) AS prev_likes,
        LAG(total_comments) OVER (PARTITION BY user_id ORDER BY quarter) AS prev_comments,
        ROUND(
            (total_views - LAG(total_views) OVER (PARTITION BY user_id ORDER BY quarter))
            ::DECIMAL
            / NULLIF(LAG(total_views) OVER (PARTITION BY user_id ORDER BY quarter), 0)
            * 100, 2
        ) AS views_growth_pct,
        ROUND(
            (total_likes - LAG(total_likes) OVER (PARTITION BY user_id ORDER BY quarter))
            ::DECIMAL
            / NULLIF(LAG(total_likes) OVER (PARTITION BY user_id ORDER BY quarter), 0)
            * 100, 2
        ) AS likes_growth_pct
    FROM quarterly_stats
)
SELECT
    TO_CHAR(quarter, 'YYYY "Q"Q') AS period,
    username,
    user_name,
    surname,
    stories_published,
    chapters_written,
    total_words,
    total_views,
    COALESCE(views_growth_pct, 0) AS views_growth_pct,
    total_likes,
    COALESCE(likes_growth_pct, 0) AS likes_growth_pct,
    total_comments,
    COALESCE(avg_rating, 0) AS avg_rating,
    RANK() OVER (
        PARTITION BY quarter
        ORDER BY total_views DESC
    ) AS rank_by_views
FROM with_growth
ORDER BY quarter DESC, rank_by_views;
}}}
=== Relational Algebra
{{{
PublishedBase ←
σ st.status = 'published'
(
  story s
  ⨝ (s.user_id = w.user_id)   writer w
  ⨝ (w.user_id = u.user_id)   users u
  ⨝ (s.story_id = st.story_id) status st
)

WithChapters ←
PublishedBase
⟕ (s.story_id = ch.story_id) chapter ch

WithLikes ←
WithChapters
⟕ (s.story_id = l.story_id) likes l

WithComments ←
WithLikes
⟕ (s.story_id = c.story_id) comment c

QuarterlyStats ←
γ
  quarter    := DATE_TRUNC('quarter', s.story_created_at),
  user_id    := u.user_id,
  username   := u.username,
  user_name  := u.user_name,
  surname    := u.surname;
  stories_published := COUNT(DISTINCT s.story_id),
  chapters_written  := COUNT(DISTINCT ch.chapter_id),
  total_views       := COALESCE(SUM(ch.view_count), 0),
  total_words       := COALESCE(SUM(ch.word_count), 0),
  total_likes       := COUNT(DISTINCT l.user_id),
  total_comments    := COUNT(DISTINCT c.comment_id),
  avg_rating        := ROUND(AVG(ch.rating), 2)
(
  WithComments
)

WithGrowth ←
π
  quarter,
  user_id,
  username,
  user_name,
  surname,
  stories_published,
  chapters_written,
  total_views,
  total_words,
  total_likes,
  total_comments,
  avg_rating,
  LAG(total_views)    OVER (PARTITION BY user_id ORDER BY quarter)
                                            → prev_views,
  LAG(total_likes)    OVER (PARTITION BY user_id ORDER BY quarter)
                                            → prev_likes,
  LAG(total_comments) OVER (PARTITION BY user_id ORDER BY quarter)
                                            → prev_comments,
  ROUND(
    (total_views - LAG(total_views) OVER (PARTITION BY user_id ORDER BY quarter))
    / NULLIF(LAG(total_views) OVER (PARTITION BY user_id ORDER BY quarter), 0)
    * 100, 2
  )                                         → views_growth_pct,
  ROUND(
    (total_likes - LAG(total_likes) OVER (PARTITION BY user_id ORDER BY quarter))
    / NULLIF(LAG(total_likes) OVER (PARTITION BY user_id ORDER BY quarter), 0)
    * 100, 2
  )                                         → likes_growth_pct
(
  QuarterlyStats
)

Result ←
π
  TO_CHAR(quarter, 'YYYY "Q"Q')             → period,
  username,
  user_name,
  surname,
  stories_published,
  chapters_written,
  total_words,
  total_views,
  COALESCE(views_growth_pct, 0)             → views_growth_pct,
  total_likes,
  COALESCE(likes_growth_pct, 0)             → likes_growth_pct,
  total_comments,
  COALESCE(avg_rating, 0)                   → avg_rating,
  RANK() OVER (PARTITION BY quarter ORDER BY total_views DESC)
                                            → rank_by_views
(
  WithGrowth
)
}}}
}}}