Changes between Version 12 and Version 13 of P6

Timestamp:

04/28/26 10:50:51 (4 days ago)

Author:

211099

Comment:

--

P6

@@ -26 +26 @@
         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
+    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),
@@ -51 +51 @@
             / 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_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
@@ -114 +114 @@
 Base ←
 story s
-⨝ (s.user_id = w.user_id)    writer w
-⨝ (w.user_id = u.user_id)    users u
+⨝ (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
+⟕ (s.story_id = ch.story_id) chapter ch
 
 WithLikes ←
 WithChapters
-⟕ (s.story_id = l.story_id)   likes l
+⟕ (s.story_id = l.story_id) likes l
 
 WithComments ←
 WithLikes
-⟕ (s.story_id = c.story_id)   comment c
+⟕ (s.story_id = c.story_id) comment c
 
 WithCollabs ←
@@ -136 +136 @@
 WithGenres ←
 WithCollabs
-⟕ (s.story_id = hg.story_id)  has_genre hg
+⟕ (s.story_id = hg.story_id) has_genre hg
 
 WithLists ←
@@ -159 +159 @@
   total_collaborators := COUNT(DISTINCT col.user_id),
   total_genres := COUNT(DISTINCT hg.genre_id),
-  saved_in_lists      := COUNT(DISTINCT rli.list_id)
+  saved_in_lists := COUNT(DISTINCT rli.list_id)
 (
   WithLists
@@ -169 +169 @@
   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_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
 (
@@ -242 +242 @@
     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'
+    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