Changes between Version 13 and Version 14 of AdvancedReports


Ignore:
Timestamp:
08/24/26 16:52:42 (13 days ago)
Author:
211099
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • AdvancedReports

    v13 v14  
    1515        u.user_id,
    1616        u.username AS writer,
    17         st.status,
     17        s.status,
    1818        COUNT(DISTINCT ch.chapter_id) AS total_chapters,
    1919        COALESCE(SUM(ch.view_count), 0) AS total_views,
    … …  
    2828    JOIN writer w   ON s.user_id = w.user_id
    2929    JOIN users u   ON w.user_id  = u.user_id
    30     JOIN status st  ON s.story_id = st.story_id
    3130    LEFT JOIN chapter ch  ON s.story_id = ch.story_id
    3231    LEFT JOIN likes l   ON s.story_id  = l.story_id
    … …  
    3837        DATE_TRUNC('quarter', s.story_created_at),
    3938        s.story_id, s.short_description, s.mature_content,
    40         u.user_id, u.username, st.status
     39        u.user_id, u.username, s.status
    4140),
    4241with_engagement AS (
    … …  
    112111==== Relational Algebra
    113112{{{
     113==== Relational Algebra
     114{{{
    114115Base ←
    115116story s
    116117⨝ (s.user_id = w.user_id) writer w
    117118⨝ (w.user_id = u.user_id) users u
    118 ⨝ (s.story_id = st.story_id) status st
    119 
    120119WithChapters ←
    121120Base
    122121⟕ (s.story_id = ch.story_id) chapter ch
    123 
    124122WithLikes ←
    125123WithChapters
    126124⟕ (s.story_id = l.story_id) likes l
    127 
    128125WithComments ←
    129126WithLikes
    130127⟕ (s.story_id = c.story_id) comment c
    131 
    132128WithCollabs ←
    133129WithComments
    134130⟕ (s.story_id = col.story_id) collaboration col
    135 
    136131WithGenres ←
    137132WithCollabs
    138133⟕ (s.story_id = hg.story_id) has_genre hg
    139 
    140134WithLists ←
    141135WithGenres
    142136⟕ (s.story_id = rli.story_id) reading_list_items rli
    143 
    144137QuarterlyStats ←
    145138γ
    … …  
    150143  user_id := u.user_id,
    151144  writer := u.username,
    152   status := st.status;
     145  status := s.status;
    153146  total_chapters := COUNT(DISTINCT ch.chapter_id),
    154147  total_views := COALESCE(SUM(ch.view_count), 0),
    … …  
    163156  WithLists
    164157)
    165 
    166158WithEngagement ←
    167159π
    … …  
    175167  QuarterlyStats
    176168)
    177 
    178169WithGrowth ←
    179170π
    … …  
    194185  WithEngagement
    195186)
    196 
    197187Result ←
    198188π
    … …  
    215205  total_comments,
    216206  COALESCE(comments_growth_pct, 0) → comments_growth_pct,
    217   COALESCE(avg_rating, 0 → avg_rating,
     207  COALESCE(avg_rating, 0) → avg_rating,
    218208  COALESCE(engagement_rate, 0) → engagement_rate,
    219209  RANK() OVER (PARTITION BY quarter ORDER BY total_views DESC) → rank_by_views,
    … …  
    242232    FROM genre g
    243233    JOIN has_genre hg ON g.genre_id = hg.genre_id
    244     JOIN story s  ON hg.story_id = s.story_id
     234    JOIN story s  ON hg.story_id = s.story_id AND s.status = 'published'
    245235    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'
    247236    LEFT JOIN chapter ch ON s.story_id = ch.story_id
    248237    LEFT JOIN likes l  ON s.story_id = l.story_id
    … …  
    305294{{{
    306295PublishedStories ←
    307 σ st.status = 'published'
     296σ s.status = 'published'
    308297(
    309298  genre g
    … …  
    311300  ⨝ (hg.story_id = s.story_id)  story s
    312301  ⨝ (s.user_id = w.user_id)     writer w
    313   ⨝ (s.story_id = st.story_id)  status st
    314 )
    315 
     302)
    316303WithChapters ←
    317304PublishedStories
    318305⟕ (s.story_id = ch.story_id) chapter ch
    319 
    320306WithLikes ←
    321307WithChapters
    322308⟕ (s.story_id = l.story_id) likes l
    323 
    324309WithComments ←
    325310WithLikes
    326311⟕ (s.story_id = c.story_id) comment c
    327 
    328312GenreAnnual ←
    329313γ
    … …  
    341325  WithComments
    342326)
    343 
    344327WithMetrics ←
    345328π
    … …  
    361344  GenreAnnual
    362345)
    363 
    364346Result ←
    365347π
    … …  
    409391    JOIN writer w  ON s.user_id    = w.user_id
    410392    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'
    412393    LEFT JOIN chapter ch ON s.story_id   = ch.story_id
    413394    LEFT JOIN likes l  ON s.story_id   = l.story_id
    414395    LEFT JOIN comment c  ON s.story_id   = c.story_id
     396    WHERE s.status = 'published'
    415397    GROUP BY
    416398        DATE_TRUNC('quarter', s.story_created_at),
    … …  
    461443{{{
    462444PublishedBase ←
    463 σ st.status = 'published'
     445σ s.status = 'published'
    464446(
    465447  story s
    466448  ⨝ (s.user_id = w.user_id)   writer w
    467449  ⨝ (w.user_id = u.user_id)   users u
    468   ⨝ (s.story_id = st.story_id) status st
    469 )
    470 
     450)
    471451WithChapters ←
    472452PublishedBase
    473453⟕ (s.story_id = ch.story_id) chapter ch
    474 
    475454WithLikes ←
    476455WithChapters
    477456⟕ (s.story_id = l.story_id) likes l
    478 
    479457WithComments ←
    480458WithLikes
    481459⟕ (s.story_id = c.story_id) comment c
    482 
    483460QuarterlyStats ←
    484461γ
    … …  
    498475  WithComments
    499476)
    500 
    501477WithGrowth ←
    502478π
    … …  
    529505  QuarterlyStats
    530506)
    531 
    532507Result ←
    533508π