| Version 11 (modified by , 7 days ago) ( diff ) |
|---|
Complex DB Reports (SQL, Stored Procedures, Relational Algebra)
1. Quarterly story performance and engagement report
SQL
WITH quarterly_story_stats AS (
SELECT
DATE_TRUNC('quarter', s.story_created_at) AS quarter,
s.story_id,
s.short_description,
s.mature_content,
u.user_id,
u.username AS writer,
st.status,
COUNT(DISTINCT ch.chapter_id) AS total_chapters,
COALESCE(SUM(ch.view_count), 0) AS total_views,
COALESCE(SUM(ch.word_count), 0) AS total_words,
ROUND(AVG(ch.rating), 2) AS avg_rating,
COUNT(DISTINCT l.user_id) AS total_likes,
COUNT(DISTINCT c.comment_id) AS total_comments,
COUNT(DISTINCT col.user_id) AS total_collaborators,
COUNT(DISTINCT hg.genre_id) AS total_genres,
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
GROUP BY
DATE_TRUNC('quarter', s.story_created_at),
s.story_id, s.short_description, s.mature_content,
u.user_id, u.username, st.status
),
with_engagement AS (
SELECT
*,
ROUND(
(total_likes + total_comments)::DECIMAL
/ NULLIF(total_views, 0) * 100, 2
) AS engagement_rate,
ROUND(
total_views::DECIMAL
/ 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_comments) OVER (PARTITION BY story_id ORDER BY quarter)
AS prev_quarter_comments
FROM quarterly_story_stats
),
with_growth AS (
SELECT
*,
ROUND(
(total_views - prev_quarter_views)::DECIMAL
/ NULLIF(prev_quarter_views, 0) * 100, 2
) AS views_growth_pct,
ROUND(
(total_likes - prev_quarter_likes)::DECIMAL
/ NULLIF(prev_quarter_likes, 0) * 100, 2
) AS likes_growth_pct,
ROUND(
(total_comments - prev_quarter_comments)::DECIMAL
/ NULLIF(prev_quarter_comments, 0) * 100, 2
) AS comments_growth_pct
FROM with_engagement
)
SELECT
TO_CHAR(quarter, 'YYYY "Q"Q') AS period,
writer,
story_id,
short_description,
status,
mature_content,
total_chapters,
total_words,
total_genres,
total_collaborators,
saved_in_lists,
total_views,
avg_views_per_chapter,
COALESCE(views_growth_pct, 0) AS views_growth_pct,
total_likes,
COALESCE(likes_growth_pct, 0) AS likes_growth_pct,
total_comments,
COALESCE(comments_growth_pct, 0) AS comments_growth_pct,
COALESCE(avg_rating, 0) AS avg_rating,
COALESCE(engagement_rate, 0) AS engagement_rate,
RANK() OVER (
PARTITION BY quarter
ORDER BY total_views DESC
) AS rank_by_views,
RANK() OVER (
PARTITION BY quarter
ORDER BY engagement_rate DESC
) AS rank_by_engagement,
RANK() OVER (
PARTITION BY quarter
ORDER BY avg_rating DESC
) AS rank_by_rating
FROM with_growth
ORDER BY quarter DESC, rank_by_views;
Relational Algebra
Base ←
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 ←
Base
⟕ (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
WithCollabs ←
WithComments
⟕ (s.story_id = col.story_id) collaboration col
WithGenres ←
WithCollabs
⟕ (s.story_id = hg.story_id) has_genre hg
WithLists ←
WithGenres
⟕ (s.story_id = rli.story_id) reading_list_items rli
QuarterlyStats ←
γ
quarter := DATE_TRUNC('quarter', s.story_created_at),
story_id := s.story_id,
short_description := s.short_description,
mature_content := s.mature_content,
user_id := u.user_id,
writer := u.username,
status := st.status;
total_chapters := COUNT(DISTINCT ch.chapter_id),
total_views := COALESCE(SUM(ch.view_count), 0),
total_words := COALESCE(SUM(ch.word_count), 0),
avg_rating := ROUND(AVG(ch.rating), 2),
total_likes := COUNT(DISTINCT l.user_id),
total_comments := COUNT(DISTINCT c.comment_id),
total_collaborators := COUNT(DISTINCT col.user_id),
total_genres := COUNT(DISTINCT hg.genre_id),
saved_in_lists := COUNT(DISTINCT rli.list_id)
(
WithLists
)
WithEngagement ←
π
*,
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_comments) OVER (PARTITION BY story_id ORDER BY quarter)
→ prev_quarter_comments
(
QuarterlyStats
)
WithGrowth ←
π
*,
ROUND(
(total_views - prev_quarter_views)
/ NULLIF(prev_quarter_views, 0) * 100, 2
) → views_growth_pct,
ROUND(
(total_likes - prev_quarter_likes)
/ NULLIF(prev_quarter_likes, 0) * 100, 2
) → likes_growth_pct,
ROUND(
(total_comments - prev_quarter_comments)
/ NULLIF(prev_quarter_comments, 0) * 100, 2
) → comments_growth_pct
(
WithEngagement
)
Result ←
π
TO_CHAR(quarter, 'YYYY "Q"Q') → period,
writer,
story_id,
short_description,
status,
mature_content,
total_chapters,
total_words,
total_genres,
total_collaborators,
saved_in_lists,
total_views,
avg_views_per_chapter,
COALESCE(views_growth_pct, 0) → views_growth_pct,
total_likes,
COALESCE(likes_growth_pct, 0) → likes_growth_pct,
total_comments,
COALESCE(comments_growth_pct, 0) → comments_growth_pct,
COALESCE(avg_rating, 0) → avg_rating,
COALESCE(engagement_rate, 0) → engagement_rate,
RANK() OVER (PARTITION BY quarter ORDER BY total_views DESC)
→ rank_by_views,
RANK() OVER (PARTITION BY quarter ORDER BY engagement_rate DESC)
→ rank_by_engagement,
RANK() OVER (PARTITION BY quarter ORDER BY avg_rating DESC)
→ rank_by_rating
(
WithGrowth
)
2. Annual genre popularity and engagement trend
SQL
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
),
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
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
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 ←
π
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
)
3. Quarterly writer performance report
SQL
WITH quarterly_stats AS (
SELECT
DATE_TRUNC('quarter', s.story_created_at) AS quarter,
u.user_id,
u.username,
u.user_name,
u.surname,
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
),
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
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
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 ←
π
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 ←
π
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
)
Note:
See TracWiki
for help on using the wiki.
