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


=== 1. Quarterly story performance and engagement report

==== SQL
{{{

WITH quarterly_story_stats AS (
    SELECT
        DATE_TRUNC('quarter', s.story_created_at) AS quarter,
        s.story_id,
        s.short_description,
        s.mature_content,
        u.user_id,
        u.username AS writer,
        st.status,
        COUNT(DISTINCT ch.chapter_id) AS total_chapters,
        COALESCE(SUM(ch.view_count), 0) AS total_views,
        COALESCE(SUM(ch.word_count), 0) AS total_words,
        ROUND(AVG(ch.rating), 2) AS avg_rating,
        COUNT(DISTINCT l.user_id) AS total_likes,
        COUNT(DISTINCT c.comment_id) AS total_comments,
        COUNT(DISTINCT col.user_id) AS total_collaborators,
        COUNT(DISTINCT hg.genre_id) AS total_genres,
        COUNT(DISTINCT rli.list_id) AS saved_in_lists
    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
    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
    LEFT JOIN collaboration col ON s.story_id   = col.story_id
    LEFT JOIN has_genre hg  ON s.story_id   = hg.story_id
    LEFT JOIN reading_list_items rli ON s.story_id  = rli.story_id
    GROUP BY
        DATE_TRUNC('quarter', s.story_created_at),
        s.story_id, s.short_description, s.mature_content,
        u.user_id, u.username, st.status
),
with_engagement AS (
    SELECT
        *,
        ROUND(
            (total_likes + total_comments)::DECIMAL
            / NULLIF(total_views, 0) * 100, 2
        ) AS engagement_rate,
        ROUND(
            total_views::DECIMAL
            / NULLIF(total_chapters, 0), 2
        ) AS avg_views_per_chapter,
        LAG(total_views)    OVER (PARTITION BY story_id ORDER BY quarter)
                                                         AS prev_quarter_views,
        LAG(total_likes)    OVER (PARTITION BY story_id ORDER BY quarter)
                                                         AS prev_quarter_likes,
        LAG(total_comments) OVER (PARTITION BY story_id ORDER BY quarter)
                                                         AS prev_quarter_comments
    FROM quarterly_story_stats
),
with_growth AS (
    SELECT
        *,
        ROUND(
            (total_views - prev_quarter_views)::DECIMAL
            / NULLIF(prev_quarter_views, 0) * 100, 2
        )                                                AS views_growth_pct,
        ROUND(
            (total_likes - prev_quarter_likes)::DECIMAL
            / NULLIF(prev_quarter_likes, 0) * 100, 2
        )                                                AS likes_growth_pct,
        ROUND(
            (total_comments - prev_quarter_comments)::DECIMAL
            / NULLIF(prev_quarter_comments, 0) * 100, 2
        )                                                AS comments_growth_pct
    FROM with_engagement
)
SELECT
    TO_CHAR(quarter, 'YYYY "Q"Q')                        AS period,
    writer,
    story_id,
    short_description,
    status,
    mature_content,
    total_chapters,
    total_words,
    total_genres,
    total_collaborators,
    saved_in_lists,
    total_views,
    avg_views_per_chapter,
    COALESCE(views_growth_pct, 0) AS views_growth_pct,
    total_likes,
    COALESCE(likes_growth_pct, 0) AS likes_growth_pct,
    total_comments,
    COALESCE(comments_growth_pct, 0) AS comments_growth_pct,
    COALESCE(avg_rating, 0) AS avg_rating,
    COALESCE(engagement_rate, 0) AS engagement_rate,
    RANK() OVER (
        PARTITION BY quarter
        ORDER BY total_views DESC
    ) AS rank_by_views,
    RANK() OVER (
        PARTITION BY quarter
        ORDER BY engagement_rate DESC
    ) AS rank_by_engagement,
    RANK() OVER (
        PARTITION BY quarter
        ORDER BY avg_rating DESC
    ) AS rank_by_rating
FROM with_growth
ORDER BY quarter DESC, rank_by_views;
}}}

==== Relational Algebra
{{{
Base ←
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 ←
Base
⟕ (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

WithCollabs ←
WithComments
⟕ (s.story_id = col.story_id) collaboration col

WithGenres ←
WithCollabs
⟕ (s.story_id = hg.story_id)  has_genre hg

WithLists ←
WithGenres
⟕ (s.story_id = rli.story_id) reading_list_items rli

QuarterlyStats ←
γ
  quarter          := DATE_TRUNC('quarter', s.story_created_at),
  story_id         := s.story_id,
  short_description := s.short_description,
  mature_content   := s.mature_content,
  user_id          := u.user_id,
  writer           := u.username,
  status           := st.status;
  total_chapters      := COUNT(DISTINCT ch.chapter_id),
  total_views         := COALESCE(SUM(ch.view_count), 0),
  total_words         := COALESCE(SUM(ch.word_count), 0),
  avg_rating          := ROUND(AVG(ch.rating), 2),
  total_likes         := COUNT(DISTINCT l.user_id),
  total_comments      := COUNT(DISTINCT c.comment_id),
  total_collaborators := COUNT(DISTINCT col.user_id),
  total_genres        := COUNT(DISTINCT hg.genre_id),
  saved_in_lists      := COUNT(DISTINCT rli.list_id)
(
  WithLists
)

WithEngagement ←
π
  *,
  ROUND((total_likes + total_comments) / NULLIF(total_views, 0) * 100, 2)
                                              → engagement_rate,
  ROUND(total_views / NULLIF(total_chapters, 0), 2)
                                              → avg_views_per_chapter,
  LAG(total_views)    OVER (PARTITION BY story_id ORDER BY quarter)
                                              → prev_quarter_views,
  LAG(total_likes)    OVER (PARTITION BY story_id ORDER BY quarter)
                                              → prev_quarter_likes,
  LAG(total_comments) OVER (PARTITION BY story_id ORDER BY quarter)
                                              → prev_quarter_comments
(
  QuarterlyStats
)

WithGrowth ←
π
  *,
  ROUND(
    (total_views - prev_quarter_views)
    / NULLIF(prev_quarter_views, 0) * 100, 2
  )                                           → views_growth_pct,
  ROUND(
    (total_likes - prev_quarter_likes)
    / NULLIF(prev_quarter_likes, 0) * 100, 2
  )                                           → likes_growth_pct,
  ROUND(
    (total_comments - prev_quarter_comments)
    / NULLIF(prev_quarter_comments, 0) * 100, 2
  )                                           → comments_growth_pct
(
  WithEngagement
)

Result ←
π
  TO_CHAR(quarter, 'YYYY "Q"Q')               → period,
  writer,
  story_id,
  short_description,
  status,
  mature_content,
  total_chapters,
  total_words,
  total_genres,
  total_collaborators,
  saved_in_lists,
  total_views,
  avg_views_per_chapter,
  COALESCE(views_growth_pct, 0)               → views_growth_pct,
  total_likes,
  COALESCE(likes_growth_pct, 0)               → likes_growth_pct,
  total_comments,
  COALESCE(comments_growth_pct, 0)            → comments_growth_pct,
  COALESCE(avg_rating, 0)                     → avg_rating,
  COALESCE(engagement_rate, 0)                → engagement_rate,
  RANK() OVER (PARTITION BY quarter ORDER BY total_views DESC)
                                              → rank_by_views,
  RANK() OVER (PARTITION BY quarter ORDER BY engagement_rate DESC)
                                              → rank_by_engagement,
  RANK() OVER (PARTITION BY quarter ORDER BY avg_rating DESC)
                                              → rank_by_rating
(
  WithGrowth
)
}}}

=== 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
)
}}}