Changes between Version 12 and Version 13 of P9

Timestamp:

05/28/26 10:38:09 (3 weeks ago)

Author:

211099

Comment:

--

P9

@@ -11 +11 @@
 
 === Without index analysis
+{{
+EXPLAIN ANALYZE
+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;
+}}
 {{{
 |QUERY PLAN                                                                                                                                                                                                                              |