Changes between Version 11 and Version 12 of P6


Ignore:
Timestamp:
04/28/26 10:43:42 (4 days ago)
Author:
211099
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • P6

    v11 v12  
    5151            / NULLIF(total_chapters, 0), 2
    5252        ) 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
     53        LAG(total_views)    OVER (PARTITION BY story_id ORDER BY quarter)  AS prev_quarter_views,
     54        LAG(total_likes)    OVER (PARTITION BY story_id ORDER BY quarter)  AS prev_quarter_likes,
     55        LAG(total_comments) OVER (PARTITION BY story_id ORDER BY quarter)  AS prev_quarter_comments
    5956    FROM quarterly_story_stats
    6057),
    … …  
    6562            (total_views - prev_quarter_views)::DECIMAL
    6663            / NULLIF(prev_quarter_views, 0) * 100, 2
    67         )                                                AS views_growth_pct,
     64        )  AS views_growth_pct,
    6865        ROUND(
    6966            (total_likes - prev_quarter_likes)::DECIMAL
    7067            / NULLIF(prev_quarter_likes, 0) * 100, 2
    71         )                                                AS likes_growth_pct,
     68        )  AS likes_growth_pct,
    7269        ROUND(
    7370            (total_comments - prev_quarter_comments)::DECIMAL
    7471            / NULLIF(prev_quarter_comments, 0) * 100, 2
    75         )                                                AS comments_growth_pct
     72        ) AS comments_growth_pct
    7673    FROM with_engagement
    7774)
    7875SELECT
    79     TO_CHAR(quarter, 'YYYY "Q"Q')                        AS period,
     76    TO_CHAR(quarter, 'YYYY "Q"Q') AS period,
    8077    writer,
    8178    story_id,
    … …  
    147144QuarterlyStats ←
    148145γ
    149   quarter          := DATE_TRUNC('quarter', s.story_created_at),
    150   story_id         := s.story_id,
     146  quarter := DATE_TRUNC('quarter', s.story_created_at),
     147  story_id := s.story_id,
    151148  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),
     149  mature_content  := s.mature_content,
     150  user_id := u.user_id,
     151  writer := u.username,
     152  status := st.status;
     153  total_chapters := COUNT(DISTINCT ch.chapter_id),
     154  total_views := COALESCE(SUM(ch.view_count), 0),
     155  total_words := COALESCE(SUM(ch.word_count), 0),
     156  avg_rating := ROUND(AVG(ch.rating), 2),
     157  total_likes := COUNT(DISTINCT l.user_id),
     158  total_comments := COUNT(DISTINCT c.comment_id),
    162159  total_collaborators := COUNT(DISTINCT col.user_id),
    163   total_genres        := COUNT(DISTINCT hg.genre_id),
     160  total_genres := COUNT(DISTINCT hg.genre_id),
    164161  saved_in_lists      := COUNT(DISTINCT rli.list_id)
    165162(
    … …  
    170167π
    171168  *,
    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
     169  ROUND((total_likes + total_comments) / NULLIF(total_views, 0) * 100, 2)  → engagement_rate,
     170  ROUND(total_views / NULLIF(total_chapters, 0), 2) → avg_views_per_chapter,
     171  LAG(total_views)    OVER (PARTITION BY story_id ORDER BY quarter) → prev_quarter_views,
     172  LAG(total_likes)    OVER (PARTITION BY story_id ORDER BY quarter) → prev_quarter_likes,
     173  LAG(total_comments) OVER (PARTITION BY story_id ORDER BY quarter) → prev_quarter_comments
    182174(
    183175  QuarterlyStats
    … …  
    190182    (total_views - prev_quarter_views)
    191183    / NULLIF(prev_quarter_views, 0) * 100, 2
    192   )                                           → views_growth_pct,
     184  ) → views_growth_pct,
    193185  ROUND(
    194186    (total_likes - prev_quarter_likes)
    195187    / NULLIF(prev_quarter_likes, 0) * 100, 2
    196   )                                           → likes_growth_pct,
     188  ) → likes_growth_pct,
    197189  ROUND(
    198190    (total_comments - prev_quarter_comments)
    199191    / NULLIF(prev_quarter_comments, 0) * 100, 2
    200   )                                           → comments_growth_pct
     192  ) → comments_growth_pct
    201193(
    202194  WithEngagement
    … …  
    205197Result ←
    206198π
    207   TO_CHAR(quarter, 'YYYY "Q"Q')               → period,
     199  TO_CHAR(quarter, 'YYYY "Q"Q') → period,
    208200  writer,
    209201  story_id,
    … …  
    218210  total_views,
    219211  avg_views_per_chapter,
    220   COALESCE(views_growth_pct, 0)               → views_growth_pct,
     212  COALESCE(views_growth_pct, 0) → views_growth_pct,
    221213  total_likes,
    222   COALESCE(likes_growth_pct, 0)               → likes_growth_pct,
     214  COALESCE(likes_growth_pct, 0) → likes_growth_pct,
    223215  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
     216  COALESCE(comments_growth_pct, 0) → comments_growth_pct,
     217  COALESCE(avg_rating, 0 → avg_rating,
     218  COALESCE(engagement_rate, 0) → engagement_rate,
     219  RANK() OVER (PARTITION BY quarter ORDER BY total_views DESC) → rank_by_views,
     220  RANK() OVER (PARTITION BY quarter ORDER BY engagement_rate DESC) → rank_by_engagement,
     221  RANK() OVER (PARTITION BY quarter ORDER BY avg_rating DESC) → rank_by_rating
    233222(
    234223  WithGrowth
    … …  
    251240        COUNT(DISTINCT c.comment_id) AS total_comments,
    252241        ROUND(AVG(ch.rating), 2) AS avg_rating
    253     FROM genre               g
    254     JOIN has_genre           hg ON g.genre_id     = hg.genre_id
    255     JOIN story               s  ON hg.story_id    = s.story_id
    256     JOIN writer              w  ON s.user_id       = w.user_id
    257     JOIN status              st ON s.story_id      = st.story_id
    258                                 AND st.status      = 'published'
    259     LEFT JOIN chapter        ch ON s.story_id      = ch.story_id
    260     LEFT JOIN likes          l  ON s.story_id      = l.story_id
    261     LEFT JOIN comment        c  ON s.story_id      = c.story_id
     242    FROM genre g
     243    JOIN has_genre hg ON g.genre_id = hg.genre_id
     244    JOIN story               s  ON hg.story_id = s.story_id
     245    JOIN writer              w  ON s.user_id = w.user_id
     246    JOIN status              st ON s.story_id = st.story_id AND st.status = 'published'
     247    LEFT JOIN chapter ch ON s.story_id = ch.story_id
     248    LEFT JOIN likes l  ON s.story_id = l.story_id
     249    LEFT JOIN comment c  ON s.story_id = c.story_id
    262250    GROUP BY
    263251        DATE_TRUNC('year', s.story_created_at),
    … …  
    343331  genre_id   := g.genre_id,
    344332  genre_name := g.genre_name;
    345   total_stories  := COUNT(DISTINCT s.story_id),
    346   total_writers  := COUNT(DISTINCT w.user_id),
    347   total_views    := COALESCE(SUM(ch.view_count), 0),
    348   total_words    := COALESCE(SUM(ch.word_count), 0),
    349   total_likes    := COUNT(DISTINCT l.user_id),
     333  total_stories := COUNT(DISTINCT s.story_id),
     334  total_writers := COUNT(DISTINCT w.user_id),
     335  total_views := COALESCE(SUM(ch.view_count), 0),
     336  total_words := COALESCE(SUM(ch.word_count), 0),
     337  total_likes := COUNT(DISTINCT l.user_id),
    350338  total_comments := COUNT(DISTINCT c.comment_id),
    351   avg_rating     := ROUND(AVG(ch.rating), 2)
     339  avg_rating := ROUND(AVG(ch.rating), 2)
    352340(
    353341  WithComments
    … …  
    366354  total_comments,
    367355  avg_rating,
    368   ROUND((total_likes + total_comments) / NULLIF(total_views, 0) * 100, 2)
    369                                           → engagement_rate,
    370   ROUND(total_views / NULLIF(total_stories, 0), 2)
    371                                           → avg_views_per_story,
    372   LAG(total_views)   OVER (PARTITION BY genre_id ORDER BY year)
    373                                           → prev_year_views,
    374   LAG(total_stories) OVER (PARTITION BY genre_id ORDER BY year)
    375                                           → prev_year_stories
     356  ROUND((total_likes + total_comments) / NULLIF(total_views, 0) * 100, 2) → engagement_rate,
     357  ROUND(total_views / NULLIF(total_stories, 0), 2) → avg_views_per_story,
     358  LAG(total_views)   OVER (PARTITION BY genre_id ORDER BY year) → prev_year_views,
     359  LAG(total_stories) OVER (PARTITION BY genre_id ORDER BY year) → prev_year_stories
    376360(
    377361  GenreAnnual
    … …  
    380364Result ←
    381365π
    382   TO_CHAR(year, 'YYYY')                   → year,
     366  TO_CHAR(year, 'YYYY') → year,
    383367  genre_name,
    384368  total_stories,
    … …  
    388372  total_likes,
    389373  total_comments,
    390   COALESCE(avg_rating, 0)                 → avg_rating,
    391   COALESCE(engagement_rate, 0)            → engagement_rate,
     374  COALESCE(avg_rating, 0) → avg_rating,
     375  COALESCE(engagement_rate, 0) → engagement_rate,
    392376  ROUND(
    393377    (total_views - prev_year_views)
    394378    / NULLIF(prev_year_views, 0) * 100, 2
    395   )                                       → yoy_views_growth_pct,
     379  ) → yoy_views_growth_pct,
    396380  ROUND(
    397381    (total_stories - prev_year_stories)
    398382    / NULLIF(prev_year_stories, 0) * 100, 2
    399   )                                       → yoy_stories_growth_pct,
    400   RANK() OVER (PARTITION BY year ORDER BY total_views DESC)
    401                                           → popularity_rank,
    402   RANK() OVER (PARTITION BY year ORDER BY engagement_rate DESC)
    403                                           → engagement_rank
     383  ) → yoy_stories_growth_pct,
     384  RANK() OVER (PARTITION BY year ORDER BY total_views DESC) → popularity_rank,
     385  RANK() OVER (PARTITION BY year ORDER BY engagement_rate DESC) → engagement_rank
    404386(
    405387  WithMetrics
    … …  
    412394WITH quarterly_stats AS (
    413395    SELECT
    414         DATE_TRUNC('quarter', s.story_created_at)   AS quarter,
     396        DATE_TRUNC('quarter', s.story_created_at) AS quarter,
    415397        u.user_id,
    416398        u.username,
    … …  
    424406        COUNT(DISTINCT c.comment_id) AS total_comments,
    425407        ROUND(AVG(ch.rating), 2) AS avg_rating
    426     FROM story               s
    427     JOIN writer              w  ON s.user_id    = w.user_id
    428     JOIN users               u  ON w.user_id    = u.user_id
    429     JOIN status              st ON s.story_id   = st.story_id
    430                                 AND st.status   = 'published'
    431     LEFT JOIN chapter        ch ON s.story_id   = ch.story_id
    432     LEFT JOIN likes          l  ON s.story_id   = l.story_id
    433     LEFT JOIN comment        c  ON s.story_id   = c.story_id
     408    FROM story s
     409    JOIN writer w  ON s.user_id    = w.user_id
     410    JOIN users  u  ON w.user_id    = u.user_id
     411    JOIN status st ON s.story_id   = st.story_id AND st.status   = 'published'
     412    LEFT JOIN chapter ch ON s.story_id   = ch.story_id
     413    LEFT JOIN likes l  ON s.story_id   = l.story_id
     414    LEFT JOIN comment c  ON s.story_id   = c.story_id
    434415    GROUP BY
    435416        DATE_TRUNC('quarter', s.story_created_at),
    … …  
    439420    SELECT
    440421        *,
    441         LAG(total_views)    OVER (PARTITION BY user_id ORDER BY quarter) AS prev_views,
    442         LAG(total_likes)    OVER (PARTITION BY user_id ORDER BY quarter) AS prev_likes,
     422        LAG(total_views)  OVER (PARTITION BY user_id ORDER BY quarter) AS prev_views,
     423        LAG(total_likes)  OVER (PARTITION BY user_id ORDER BY quarter) AS prev_likes,
    443424        LAG(total_comments) OVER (PARTITION BY user_id ORDER BY quarter) AS prev_comments,
    444425        ROUND(
    … …  
    502483QuarterlyStats ←
    503484γ
    504   quarter    := DATE_TRUNC('quarter', s.story_created_at),
    505   user_id    := u.user_id,
    506   username   := u.username,
    507   user_name  := u.user_name,
    508   surname    := u.surname;
     485  quarter := DATE_TRUNC('quarter', s.story_created_at),
     486  user_id := u.user_id,
     487  username := u.username,
     488  user_name := u.user_name,
     489  surname := u.surname;
    509490  stories_published := COUNT(DISTINCT s.story_id),
    510491  chapters_written  := COUNT(DISTINCT ch.chapter_id),
    511   total_views       := COALESCE(SUM(ch.view_count), 0),
    512   total_words       := COALESCE(SUM(ch.word_count), 0),
    513   total_likes       := COUNT(DISTINCT l.user_id),
    514   total_comments    := COUNT(DISTINCT c.comment_id),
    515   avg_rating        := ROUND(AVG(ch.rating), 2)
     492  total_views := COALESCE(SUM(ch.view_count), 0),
     493  total_words := COALESCE(SUM(ch.word_count), 0),
     494  total_likes := COUNT(DISTINCT l.user_id),
     495  total_comments := COUNT(DISTINCT c.comment_id),
     496  avg_rating := ROUND(AVG(ch.rating), 2)
    516497(
    517498  WithComments
    … …  
    532513  total_comments,
    533514  avg_rating,
    534   LAG(total_views)    OVER (PARTITION BY user_id ORDER BY quarter)
    535                                             → prev_views,
    536   LAG(total_likes)    OVER (PARTITION BY user_id ORDER BY quarter)
    537                                             → prev_likes,
    538   LAG(total_comments) OVER (PARTITION BY user_id ORDER BY quarter)
    539                                             → prev_comments,
     515  LAG(total_views) OVER (PARTITION BY user_id ORDER BY quarter)  → prev_views,
     516  LAG(total_likes) OVER (PARTITION BY user_id ORDER BY quarter)  → prev_likes,
     517  LAG(total_comments) OVER (PARTITION BY user_id ORDER BY quarter) → prev_comments,
    540518  ROUND(
    541519    (total_views - LAG(total_views) OVER (PARTITION BY user_id ORDER BY quarter))
    542520    / NULLIF(LAG(total_views) OVER (PARTITION BY user_id ORDER BY quarter), 0)
    543521    * 100, 2
    544   )                                         → views_growth_pct,
     522  )  → views_growth_pct,
    545523  ROUND(
    546524    (total_likes - LAG(total_likes) OVER (PARTITION BY user_id ORDER BY quarter))
    547525    / NULLIF(LAG(total_likes) OVER (PARTITION BY user_id ORDER BY quarter), 0)
    548526    * 100, 2
    549   )                                         → likes_growth_pct
     527  ) → likes_growth_pct
    550528(
    551529  QuarterlyStats
    … …  
    554532Result ←
    555533π
    556   TO_CHAR(quarter, 'YYYY "Q"Q')             → period,
     534  TO_CHAR(quarter, 'YYYY "Q"Q') → period,
    557535  username,
    558536  user_name,
    … …  
    562540  total_words,
    563541  total_views,
    564   COALESCE(views_growth_pct, 0)             → views_growth_pct,
     542  COALESCE(views_growth_pct, 0) → views_growth_pct,
    565543  total_likes,
    566   COALESCE(likes_growth_pct, 0)             → likes_growth_pct,
     544  COALESCE(likes_growth_pct, 0) → likes_growth_pct,
    567545  total_comments,
    568   COALESCE(avg_rating, 0)                   → avg_rating,
    569   RANK() OVER (PARTITION BY quarter ORDER BY total_views DESC)
    570                                             → rank_by_views
     546  COALESCE(avg_rating, 0) → avg_rating,
     547  RANK() OVER (PARTITION BY quarter ORDER BY total_views DESC) → rank_by_views
    571548(
    572549  WithGrowth