Cross-Platform Engagement Correlation

WITH post_counts AS (SELECT user_id, COUNT(*) AS posts_count FROM posts GROUP BY user_id), comment_counts AS (SELECT user_id, COUNT(*) AS comments_count FROM comments GROUP BY user_id), like_counts AS (SELECT user_id, COUNT(*) AS likes_count FROM likes GROUP BY user_id), message_counts AS (SELECT sender_id AS user_id, COUNT(*) AS messages_count FROM messages GROUP BY sender_id), story_counts AS (SELECT user_id, COUNT(*) AS stories_count FROM stories GROUP BY user_id), user_activity AS (SELECT u.user_id, u.username, COALESCE(p.posts_count, 0) AS posts_count, COALESCE(c.comments_count, 0) AS comments_count, COALESCE(l.likes_count, 0) AS likes_count, COALESCE(m.messages_count, 0) AS messages_count, COALESCE(s.stories_count, 0) AS stories_count FROM users u LEFT JOIN post_counts p ON u.user_id = p.user_id LEFT JOIN comment_counts c ON u.user_id = c.user_id LEFT JOIN like_counts l ON u.user_id = l.user_id LEFT JOIN message_counts m ON u.user_id = m.user_id LEFT JOIN story_counts s ON u.user_id = s.user_id WHERE u.status = 'active'), engagement_type AS (SELECT user_id, username, posts_count, comments_count, likes_count, messages_count, stories_count, CASE WHEN posts_count >= comments_count AND posts_count >= likes_count AND posts_count >= messages_count AND posts_count >= stories_count THEN 'Content Creator' WHEN comments_count >= likes_count AND comments_count >= messages_count AND comments_count >= stories_count THEN 'Commenter' WHEN likes_count >= messages_count AND likes_count >= stories_count THEN 'Liker' WHEN messages_count >= stories_count THEN 'Messenger' ELSE 'Story Viewer' END AS primary_engagement_type, posts_count * 5 + comments_count * 3 + likes_count + messages_count * 2 + stories_count * 4 AS total_activity_score FROM user_activity) SELECT username, posts_count, comments_count, likes_count, messages_count, stories_count, primary_engagement_type, RANK() OVER (ORDER BY total_activity_score DESC) AS activity_rank FROM engagement_type ORDER BY activity_rank, username;

Why this works

Aggregate each one-to-many activity surface independently before joining it to users. This avoids a large Cartesian fan-out and makes every count correct by construction.

Success check

4 active users with fan-out-safe surface counts; john_doe ranks first after both of his stories are counted.