User Engagement — Reach, Resonance & Activity Patterns

Engagement rate measures how much a user's audience interacts relative to their reach. We compute engagement rate, posting cadence, and top performers.

-- User engagement leaderboard
WITH user_stats AS (
    SELECT
        u.id,
        u.username,
        u.verified,
        u.follower_count,
        COUNT(t.id)                                     AS tweet_count_90d,
        SUM(t.like_count)                               AS total_likes,
        SUM(t.retweet_count)                            AS total_retweets,
        SUM(t.reply_count)                              AS total_replies,
        SUM(t.impression_count)                         AS total_impressions,
        AVG(t.like_count)                               AS avg_likes_per_tweet,
        MAX(t.like_count)                               AS best_tweet_likes
    FROM tw_users u
    JOIN tw_tweets t ON t.user_id = u.id
    WHERE t.retweet_of_id IS NULL   -- original content only
      AND t.created_at >= NOW() - INTERVAL '90 days'
    GROUP BY u.id, u.username, u.verified, u.follower_count
    HAVING COUNT(t.id) >= 5
)
SELECT
    username,
    verified,
    follower_count,
    tweet_count_90d,
    total_likes,
    total_retweets,
    -- Engagement rate = (likes + retweets + replies) / impressions
    ROUND(100.0 * (total_likes + total_retweets + total_replies)
          / NULLIF(total_impressions, 0), 3)             AS engagement_rate_pct,
    ROUND(avg_likes_per_tweet, 1)                        AS avg_likes_per_tweet,
    best_tweet_likes,
    -- Follower efficiency: likes per 1000 followers
    ROUND(1000.0 * total_likes / NULLIF(follower_count, 0), 2)
                                                         AS likes_per_1k_followers
FROM user_stats
ORDER BY engagement_rate_pct DESC
LIMIT 20;