| Version 7 (modified by , 7 days ago) ( diff ) |
|---|
Complex DB Reports (SQL, Stored Procedures, Relational Algebra)
1. Story Statistics
SQL
WITH likeCount AS (
SELECT
story_id,
COUNT(user_id) AS total_likes
FROM likes
GROUP BY story_id
),
commentCount AS (
SELECT
story_id,
COUNT(comment_id) AS total_comments
FROM comment
GROUP BY story_id
),
averageRating AS (
SELECT
story_id,
ROUND(AVG(rating), 2) AS avg_rating
FROM chapter
WHERE rating IS NOT NULL
GROUP BY story_id
)
SELECT
u.username AS writer,
s.story_id,
s.short_description,
st.status,
COALESCE(lk.total_likes, 0) AS total_likes,
COALESCE(cm.total_comments, 0) AS total_comments,
COALESCE(CAST(ar.avg_rating AS VARCHAR), 'no ratings') AS avg_rating
FROM story s
JOIN status st ON s.story_id = st.story_id
JOIN writer w ON s.user_id = w.user_id
JOIN users u ON w.user_id = u.user_id
LEFT JOIN likeCount lk ON s.story_id = lk.story_id
LEFT JOIN commentCount cm ON s.story_id = cm.story_id
LEFT JOIN averageRating ar ON s.story_id = ar.story_id
ORDER BY total_likes DESC, total_comments DESC;
Relational Algebra
likeCount <- γ story_id; total_likes := COUNT(user_id) ( likes ) commentCount <- γ story_id; total_comments := COUNT(comment_id) ( comment ) averageRating <- γ story_id; avg_rating := ROUND(AVG(rating), 2) ( σ rating ≠ NULL (chapter) ) Base <- story s ⨝ (s.story_id = st.story_id) status st ⨝ (s.user_id = w.user_id) writer w ⨝ (w.user_id = u.user_id) users u WithLikes <- Base ⟕ (s.story_id = lk.story_id) likeCount lk WithComments <- WithLikes ⟕ (s.story_id = cm.story_id) commentCount cm WithRatings <- WithComments ⟕ (s.story_id = ar.story_id) averageRating ar Result <- π u.username → writer, s.story_id, s.short_description, st.status, COALESCE(lk.total_likes, 0) → total_likes, COALESCE(cm.total_comments, 0) → total_comments, COALESCE(ar.avg_rating, 'no ratings') → avg_rating ( WithRatings )
2. Validate and insert story into user reading list with confirmation
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
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 ←
π
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.Detailed report on percentage change in collaborator count and invitation validation for a story
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.
