User Influence Score Dashboard

WITH user_posts AS (SELECT p.user_id, COUNT(p.post_id) AS total_posts, ROUND(AVG(p.likes_count + p.comments_count + p.shares_count), 2) AS avg_engagement_per_post, MAX(p.likes_count + p.comments_count + p.shares_count) AS max_engagement FROM posts p GROUP BY p.user_id), user_friends AS (SELECT user_id_1 AS user_id, user_id_2 AS friend_id FROM friendships WHERE status = 'accepted' UNION ALL SELECT user_id_2 AS user_id, user_id_1 AS friend_id FROM friendships WHERE status = 'accepted'), friend_counts AS (SELECT user_id, COUNT(friend_id) AS friend_count FROM user_friends GROUP BY user_id), influence_calc AS (SELECT u.user_id, u.username, COALESCE(up.total_posts, 0) AS total_posts, COALESCE(up.avg_engagement_per_post, 0) AS avg_engagement_per_post, COALESCE(fc.friend_count, 0) AS friend_count, ROUND((COALESCE(up.total_posts, 0) * 10 + COALESCE(up.avg_engagement_per_post, 0) * 2 + COALESCE(fc.friend_count, 0) * 15 + COALESCE(up.max_engagement, 0) * 0.5), 2) AS influence_score FROM users u LEFT JOIN user_posts up ON u.user_id = up.user_id LEFT JOIN friend_counts fc ON u.user_id = fc.user_id WHERE u.status = 'active') SELECT username, total_posts, avg_engagement_per_post, friend_count, influence_score, RANK() OVER (ORDER BY influence_score DESC) AS influence_rank FROM influence_calc ORDER BY influence_rank, username;

Why this works

COALESCE handles users with no posts (their avg/max would be NULL). The formula rewards engagement quality (avg*2) and network size (friends*15) more than raw post count (posts*10). alex_brown is excluded because status = 'inactive'.

Success check

4 active users — jane_smith leads (619.5), john_doe ranks last (232)