Content Virality Score

WITH post_engagement AS (SELECT p.post_id, u.username, SUBSTR(p.content, 1, 40) AS content_preview, (p.likes_count + p.comments_count + p.shares_count) AS total_engagement, p.shares_count * 2.0 + p.comments_count * 1.5 + p.likes_count AS weighted_engagement FROM posts p INNER JOIN users u ON p.user_id = u.user_id WHERE (p.likes_count + p.comments_count + p.shares_count) > 0), virality_calc AS (SELECT post_id, username, content_preview, total_engagement, ROUND(weighted_engagement * 100.0 / NULLIF((SELECT MAX(weighted_engagement) FROM post_engagement), 0), 2) AS virality_score FROM post_engagement) SELECT post_id, username, content_preview, total_engagement, virality_score, RANK() OVER (ORDER BY virality_score DESC) AS virality_rank FROM virality_calc ORDER BY virality_rank, post_id;

Why this works

Compute one weighted score per post, normalize it against the maximum with a protected denominator, and rank the resulting scores. The measure now matches the stated virality definition without an unrelated likes-based ratio.

Success check

6 posts — jane_smith's 'Thoughts on remote work' scores 100 (top), john_doe's project post scores 18.5 (bottom)