Changes between Version 9 and Version 10 of P6


Ignore:
Timestamp:
04/25/26 22:12:19 (7 days ago)
Author:
211099
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • P6

    v9 v10  
    22
    33
    4 === 1. Story Statistics
     4=== 1. Quarterly story performance and engagement report
    55
    66==== SQL
    77{{{
    88
    9 WITH likeCount AS (
    10     SELECT
    11         story_id,
    12         COUNT(user_id) AS total_likes
    13     FROM likes
    14     GROUP BY story_id
     9WITH quarterly_story_stats AS (
     10    SELECT
     11        DATE_TRUNC('quarter', s.story_created_at) AS quarter,
     12        s.story_id,
     13        s.short_description,
     14        s.mature_content,
     15        u.user_id,
     16        u.username AS writer,
     17        st.status,
     18        COUNT(DISTINCT ch.chapter_id) AS total_chapters,
     19        COALESCE(SUM(ch.view_count), 0) AS total_views,
     20        COALESCE(SUM(ch.word_count), 0) AS total_words,
     21        ROUND(AVG(ch.rating), 2) AS avg_rating,
     22        COUNT(DISTINCT l.user_id) AS total_likes,
     23        COUNT(DISTINCT c.comment_id) AS total_comments,
     24        COUNT(DISTINCT col.user_id) AS total_collaborators,
     25        COUNT(DISTINCT hg.genre_id) AS total_genres,
     26        COUNT(DISTINCT rli.list_id) AS saved_in_lists
     27    FROM story s
     28    JOIN writer w   ON s.user_id    = w.user_id
     29    JOIN users u   ON w.user_id    = u.user_id
     30    JOIN status st  ON s.story_id   = st.story_id
     31    LEFT JOIN chapter ch  ON s.story_id   = ch.story_id
     32    LEFT JOIN likes l   ON s.story_id   = l.story_id
     33    LEFT JOIN comment c   ON s.story_id   = c.story_id
     34    LEFT JOIN collaboration col ON s.story_id   = col.story_id
     35    LEFT JOIN has_genre hg  ON s.story_id   = hg.story_id
     36    LEFT JOIN reading_list_items rli ON s.story_id  = rli.story_id
     37    GROUP BY
     38        DATE_TRUNC('quarter', s.story_created_at),
     39        s.story_id, s.short_description, s.mature_content,
     40        u.user_id, u.username, st.status
    1541),
    16 commentCount AS (
    17     SELECT
    18         story_id,
    19         COUNT(comment_id) AS total_comments
    20     FROM comment
    21     GROUP BY story_id
     42with_engagement AS (
     43    SELECT
     44        *,
     45        ROUND(
     46            (total_likes + total_comments)::DECIMAL
     47            / NULLIF(total_views, 0) * 100, 2
     48        ) AS engagement_rate,
     49        ROUND(
     50            total_views::DECIMAL
     51            / NULLIF(total_chapters, 0), 2
     52        ) AS avg_views_per_chapter,
     53        LAG(total_views)    OVER (PARTITION BY story_id ORDER BY quarter)
     54                                                         AS prev_quarter_views,
     55        LAG(total_likes)    OVER (PARTITION BY story_id ORDER BY quarter)
     56                                                         AS prev_quarter_likes,
     57        LAG(total_comments) OVER (PARTITION BY story_id ORDER BY quarter)
     58                                                         AS prev_quarter_comments
     59    FROM quarterly_story_stats
    2260),
    23 averageRating AS (
    24     SELECT
    25         story_id,
    26         ROUND(AVG(rating), 2) AS avg_rating
    27     FROM chapter
    28     WHERE rating IS NOT NULL
    29     GROUP BY story_id
     61with_growth AS (
     62    SELECT
     63        *,
     64        ROUND(
     65            (total_views - prev_quarter_views)::DECIMAL
     66            / NULLIF(prev_quarter_views, 0) * 100, 2
     67        )                                                AS views_growth_pct,
     68        ROUND(
     69            (total_likes - prev_quarter_likes)::DECIMAL
     70            / NULLIF(prev_quarter_likes, 0) * 100, 2
     71        )                                                AS likes_growth_pct,
     72        ROUND(
     73            (total_comments - prev_quarter_comments)::DECIMAL
     74            / NULLIF(prev_quarter_comments, 0) * 100, 2
     75        )                                                AS comments_growth_pct
     76    FROM with_engagement
    3077)
    3178SELECT
    32     u.username AS writer,
    33     s.story_id,
    34     s.short_description,
    35     st.status,
    36     COALESCE(lk.total_likes, 0) AS total_likes,
    37     COALESCE(cm.total_comments, 0)         AS total_comments,
    38     COALESCE(CAST(ar.avg_rating AS VARCHAR), 'no ratings')    AS avg_rating
    39 FROM story              s
    40 JOIN status             st ON s.story_id = st.story_id
    41 JOIN writer             w  ON s.user_id  = w.user_id
    42 JOIN users              u  ON w.user_id  = u.user_id
    43 LEFT JOIN likeCount     lk ON s.story_id = lk.story_id
    44 LEFT JOIN commentCount  cm ON s.story_id = cm.story_id
    45 LEFT JOIN averageRating ar ON s.story_id = ar.story_id
    46 ORDER BY total_likes DESC, total_comments DESC;
     79    TO_CHAR(quarter, 'YYYY "Q"Q')                        AS period,
     80    writer,
     81    story_id,
     82    short_description,
     83    status,
     84    mature_content,
     85    total_chapters,
     86    total_words,
     87    total_genres,
     88    total_collaborators,
     89    saved_in_lists,
     90    total_views,
     91    avg_views_per_chapter,
     92    COALESCE(views_growth_pct, 0) AS views_growth_pct,
     93    total_likes,
     94    COALESCE(likes_growth_pct, 0) AS likes_growth_pct,
     95    total_comments,
     96    COALESCE(comments_growth_pct, 0) AS comments_growth_pct,
     97    COALESCE(avg_rating, 0) AS avg_rating,
     98    COALESCE(engagement_rate, 0) AS engagement_rate,
     99    RANK() OVER (
     100        PARTITION BY quarter
     101        ORDER BY total_views DESC
     102    ) AS rank_by_views,
     103    RANK() OVER (
     104        PARTITION BY quarter
     105        ORDER BY engagement_rate DESC
     106    ) AS rank_by_engagement,
     107    RANK() OVER (
     108        PARTITION BY quarter
     109        ORDER BY avg_rating DESC
     110    ) AS rank_by_rating
     111FROM with_growth
     112ORDER BY quarter DESC, rank_by_views;
    47113}}}
    48114
    49115==== Relational Algebra
    50116{{{
    51 likeCount <-
    52 γ story_id;
    53   total_likes := COUNT(user_id)
    54 (
    55   likes
    56 )
    57 
    58 commentCount <-
    59 γ story_id;
    60   total_comments := COUNT(comment_id)
    61 (
    62   comment
    63 )
    64 
    65 averageRating <-
    66 γ story_id;
    67   avg_rating := ROUND(AVG(rating), 2)
    68 (
    69   σ rating ≠ NULL (chapter)
    70 )
    71 
    72 Base <-
     117Base ←
    73118story s
     119⨝ (s.user_id = w.user_id)    writer w
     120⨝ (w.user_id = u.user_id)    users u
    74121⨝ (s.story_id = st.story_id) status st
    75 ⨝ (s.user_id = w.user_id)   writer w
    76 ⨝ (w.user_id = u.user_id)   users u
    77 
    78 WithLikes <-
     122
     123WithChapters ←
    79124Base
    80 ⟕ (s.story_id = lk.story_id) likeCount lk
    81 
    82 WithComments <-
     125⟕ (s.story_id = ch.story_id)  chapter ch
     126
     127WithLikes ←
     128WithChapters
     129⟕ (s.story_id = l.story_id)   likes l
     130
     131WithComments ←
    83132WithLikes
    84 ⟕ (s.story_id = cm.story_id) commentCount cm
    85 
    86 WithRatings <-
     133⟕ (s.story_id = c.story_id)   comment c
     134
     135WithCollabs ←
    87136WithComments
    88 ⟕ (s.story_id = ar.story_id) averageRating ar
    89 
    90 Result <-
    91 π
    92   u.username → writer,
    93   s.story_id,
    94   s.short_description,
    95   st.status,
    96   COALESCE(lk.total_likes, 0)             → total_likes,
    97   COALESCE(cm.total_comments, 0)          → total_comments,
    98   COALESCE(ar.avg_rating, 'no ratings')   → avg_rating
    99 (
    100   WithRatings
     137⟕ (s.story_id = col.story_id) collaboration col
     138
     139WithGenres ←
     140WithCollabs
     141⟕ (s.story_id = hg.story_id)  has_genre hg
     142
     143WithLists ←
     144WithGenres
     145⟕ (s.story_id = rli.story_id) reading_list_items rli
     146
     147QuarterlyStats ←
     148γ
     149  quarter          := DATE_TRUNC('quarter', s.story_created_at),
     150  story_id         := s.story_id,
     151  short_description := s.short_description,
     152  mature_content   := s.mature_content,
     153  user_id          := u.user_id,
     154  writer           := u.username,
     155  status           := st.status;
     156  total_chapters      := COUNT(DISTINCT ch.chapter_id),
     157  total_views         := COALESCE(SUM(ch.view_count), 0),
     158  total_words         := COALESCE(SUM(ch.word_count), 0),
     159  avg_rating          := ROUND(AVG(ch.rating), 2),
     160  total_likes         := COUNT(DISTINCT l.user_id),
     161  total_comments      := COUNT(DISTINCT c.comment_id),
     162  total_collaborators := COUNT(DISTINCT col.user_id),
     163  total_genres        := COUNT(DISTINCT hg.genre_id),
     164  saved_in_lists      := COUNT(DISTINCT rli.list_id)
     165(
     166  WithLists
     167)
     168
     169WithEngagement ←
     170π
     171  *,
     172  ROUND((total_likes + total_comments) / NULLIF(total_views, 0) * 100, 2)
     173                                              → engagement_rate,
     174  ROUND(total_views / NULLIF(total_chapters, 0), 2)
     175                                              → avg_views_per_chapter,
     176  LAG(total_views)    OVER (PARTITION BY story_id ORDER BY quarter)
     177                                              → prev_quarter_views,
     178  LAG(total_likes)    OVER (PARTITION BY story_id ORDER BY quarter)
     179                                              → prev_quarter_likes,
     180  LAG(total_comments) OVER (PARTITION BY story_id ORDER BY quarter)
     181                                              → prev_quarter_comments
     182(
     183  QuarterlyStats
     184)
     185
     186WithGrowth ←
     187π
     188  *,
     189  ROUND(
     190    (total_views - prev_quarter_views)
     191    / NULLIF(prev_quarter_views, 0) * 100, 2
     192  )                                           → views_growth_pct,
     193  ROUND(
     194    (total_likes - prev_quarter_likes)
     195    / NULLIF(prev_quarter_likes, 0) * 100, 2
     196  )                                           → likes_growth_pct,
     197  ROUND(
     198    (total_comments - prev_quarter_comments)
     199    / NULLIF(prev_quarter_comments, 0) * 100, 2
     200  )                                           → comments_growth_pct
     201(
     202  WithEngagement
     203)
     204
     205Result ←
     206π
     207  TO_CHAR(quarter, 'YYYY "Q"Q')               → period,
     208  writer,
     209  story_id,
     210  short_description,
     211  status,
     212  mature_content,
     213  total_chapters,
     214  total_words,
     215  total_genres,
     216  total_collaborators,
     217  saved_in_lists,
     218  total_views,
     219  avg_views_per_chapter,
     220  COALESCE(views_growth_pct, 0)               → views_growth_pct,
     221  total_likes,
     222  COALESCE(likes_growth_pct, 0)               → likes_growth_pct,
     223  total_comments,
     224  COALESCE(comments_growth_pct, 0)            → comments_growth_pct,
     225  COALESCE(avg_rating, 0)                     → avg_rating,
     226  COALESCE(engagement_rate, 0)                → engagement_rate,
     227  RANK() OVER (PARTITION BY quarter ORDER BY total_views DESC)
     228                                              → rank_by_views,
     229  RANK() OVER (PARTITION BY quarter ORDER BY engagement_rate DESC)
     230                                              → rank_by_engagement,
     231  RANK() OVER (PARTITION BY quarter ORDER BY avg_rating DESC)
     232                                              → rank_by_rating
     233(
     234  WithGrowth
    101235)
    102236}}}