Changes between Version 6 and Version 7 of P6

Timestamp:

04/25/26 22:04:02 (7 days ago)

Author:

211099

Comment:

--

P6

@@ -105 +105 @@
 ==== SQL
 {{{ 
-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'
+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
 ),
-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
-),
-list_items AS (
-    SELECT
-        rli.list_id,
-        COUNT(rli.story_id) AS total_items,
-        MAX(rli.added_at)   AS last_added_at
-    FROM reading_list_items rli
-    WHERE rli.list_id = 1
-    GROUP BY rli.list_id
+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
-    rl.list_id,
-    rl.list_name,
-    rl.is_public,
-    u.username                              AS owner,
-    ps.story_id                             AS story_to_add,
-    ps.short_description                    AS story_description,
-    COALESCE(li.total_items, 0)             AS total_items,
-    li.last_added_at,
-    CASE
-        WHEN NOT EXISTS (SELECT 1 FROM published_story) THEN 'Failed: story not published or not found'
-        WHEN NOT EXISTS (SELECT 1 FROM user_list)       THEN 'Failed: reading list not found or not owned by you'
-        WHEN EXISTS     (SELECT 1 FROM already_added)   THEN 'Story already in list'
-        ELSE                                                 'Ready to add story'
-    END                                     AS status
-FROM reading_list               rl
-JOIN users                      u   ON rl.user_id  = u.user_id
-LEFT JOIN published_story       ps  ON ps.story_id = 1
-LEFT JOIN list_items            li  ON li.list_id  = rl.list_id
-WHERE rl.list_id = 1;
+    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
 {{{
-ValidateAndInsertStoryIntoUserReadingListWithConfirmation
-
-
-PublishedStory ←
-π story_id, short_description
-(
-  σ s.story_id = 1 ∧ st.status = 'published'
-  (
-    story s
-    ⨝ (s.story_id = st.story_id) status st
-  )
-)
-
-UserList ←
-π list_id, list_name, is_public
-(
-  σ list_id = 1 ∧ user_id = 4
-  (
-    reading_list
-  )
-)
-
-AlreadyAdded ←
-σ list_id = 1 ∧ story_id = 1
-(
-  reading_list_items
-)
-
-ListItems ←
-γ list_id;
-  total_items  := COUNT(story_id),
-  last_added_at := MAX(added_at)
-(
-  σ list_id = 1
-  (
-    reading_list_items
-  )
+Annual Genre Popularity and Engagement Trend
+
+
+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 ←
 π
-  rl.list_id,
-  rl.list_name,
-  rl.is_public,
-  u.username                                        → owner,
-  ps.story_id                                       → story_to_add,
-  ps.short_description                              → story_description,
-  COALESCE(li.total_items, 0)                       → total_items,
-  li.last_added_at,
-  CASE
-    WHEN PublishedStory = ∅   → 'Failed: story not published or not found'
-    WHEN UserList = ∅         → 'Failed: reading list not found or not owned by you'
-    WHEN AlreadyAdded ≠ ∅     → 'Story already in list'
-    ELSE                      → 'Ready to add story'
-  END                                               → status
-(
-  (
-    (
-      σ rl.list_id = 1 (reading_list rl)
-      ⨝ (rl.user_id = u.user_id) users u
-    )
-    ⟕ (ps.story_id = 1) PublishedStory ps
-  )
-  ⟕ (li.list_id = rl.list_id) ListItems li
+  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
 )
 }}}
@@ -228 +279 @@
 ==== SQL
 {{{
-WITH
-owned_story AS (
-    SELECT s.story_id, s.short_description
-    FROM story s
-    JOIN writer w ON s.user_id = w.user_id
-    WHERE s.story_id = 5
-      AND s.user_id = :owner_user_id
-),
-invited_writer AS (
-    SELECT u.user_id, u.username, u.user_name, u.surname
-    FROM users u
-    JOIN writer w ON u.user_id = w.user_id
-    WHERE u.username = :invited_username
-),
-already_collaborating AS (
-    SELECT 1
-    FROM collaboration
-    WHERE user_id  = (SELECT user_id  FROM invited_writer)
-      AND story_id = (SELECT story_id FROM owned_story)
-),
-current_collaborators AS (
-    SELECT
+WITH quarterly_stats AS (
+    SELECT
+        DATE_TRUNC('quarter', s.story_created_at)   AS quarter,
+        u.user_id,
         u.username,
         u.user_name,
         u.surname,
-        r.roles,
-        p.permission_level,
-        c.collab_created_at
-    FROM collaboration    c
-    JOIN users            u ON c.user_id  = u.user_id
-    JOIN roles            r ON c.user_id  = r.user_id AND c.story_id = r.story_id
-    JOIN permission_level p ON c.user_id  = p.user_id AND c.story_id = p.story_id
-    WHERE c.story_id = (SELECT story_id FROM owned_story)
+        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
 ),
-notification_preview AS (
-    SELECT
-        iw.user_id AS recipient_user_id,
-        iw.username AS recipient_username,
-        'You have been invited to collaborate on "'
-            || os.short_description || '"' AS notification_content,
-        'collaboration' AS type,
-        '/story/' || os.story_id::VARCHAR   AS link
-    FROM invited_writer iw
-    CROSS JOIN owned_story os
+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
-    cc.username,
-    cc.user_name,
-    cc.surname,
-    cc.roles,
-    cc.permission_level,
-    cc.collab_created_at,
-    COUNT(*) OVER () AS total_collaborators,
-    np.notification_content AS pending_notification,
-    CASE
-        WHEN NOT EXISTS (SELECT 1 FROM owned_story)          THEN 'Failed: story not found or not owned by you'
-        WHEN NOT EXISTS (SELECT 1 FROM invited_writer)       THEN 'Failed: invited user is not a writer'
-        WHEN EXISTS     (SELECT 1 FROM already_collaborating) THEN 'User is already a collaborator'
-        ELSE                                                       'Ready to add collaborator'
-    END   AS status
-FROM current_collaborators          cc
-LEFT JOIN notification_preview      np ON np.recipient_username = :invited_username
-ORDER BY cc.collab_created_at;
+    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
 {{{
-OwnedStory ←
-π story_id, short_description
-(
-  σ s.story_id = :story_id ∧ s.user_id = :owner_user_id
-  (
-    story s
-    ⨝ (s.user_id = w.user_id) writer w
-  )
-)
-
-InvitedWriter ←
-π user_id, username, user_name, surname
-(
-  σ u.username = :invited_username
-  (
-    users u
-    ⨝ (u.user_id = w.user_id) writer w
-  )
-)
-
-AlreadyCollaborating ←
-σ user_id = (π user_id (InvitedWriter))
-  ∧ story_id = (π story_id (OwnedStory))
-(
-  collaboration
-)
-
-CurrentCollaborators ←
+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 ←
 π
-  u.username, u.user_name, u.surname,
-  r.roles, p.permission_level,
-  c.collab_created_at
-(
-  (
-    (
-      σ story_id = (π story_id (OwnedStory)) (collaboration c)
-      ⨝ (c.user_id = u.user_id) users u
-    )
-    ⨝ (c.user_id = r.user_id ∧ c.story_id = r.story_id) roles r
-  )
-  ⨝ (c.user_id = p.user_id ∧ c.story_id = p.story_id) permission_level p
-)
-
-NotificationPreview ←
-π
-  iw.user_id        → recipient_user_id,
-  iw.username       → recipient_username,
-  'You have been invited to collaborate on "' || os.short_description || '"'
-                    → notification_content,
-  'collaboration'   → type,
-  '/story/' || os.story_id → link
-(
-  InvitedWriter iw
-  ⨯ OwnedStory os
+  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 ←
 π
-  cc.username,
-  cc.user_name,
-  cc.surname,
-  cc.roles,
-  cc.permission_level,
-  cc.collab_created_at,
-  COUNT(*) OVER ()  → total_collaborators,
-  np.notification_content → pending_notification,
-  CASE
-    WHEN OwnedStory = ∅           → 'Failed: story not found or not owned by you'
-    WHEN InvitedWriter = ∅        → 'Failed: invited user is not a writer'
-    WHEN AlreadyCollaborating ≠ ∅ → 'User is already a collaborator'
-    ELSE                          → 'Ready to add collaborator'
-  END               → status
-(
-  CurrentCollaborators cc
-  ⟕ (cc.username = np.recipient_username) NotificationPreview np
-)
-}}}
+  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
+)
+}}}
+}}}