Follower Network — Graph Metrics & Influence Scoring

The follow graph reveals structural influence — not just who has the most followers but who sits at the centre of information flow. We compute follower/following ratios, mutual follows, and a simplified PageRank-like influence score.

-- Follower graph metrics per user
WITH follow_stats AS (
    SELECT
        u.id,
        u.username,
        u.verified,
        u.follower_count,
        u.following_count,
        -- Actual counts from the follow graph
        COUNT(DISTINCT f_in.follower_id)                AS graph_followers,
        COUNT(DISTINCT f_out.followee_id)               AS graph_following
    FROM tw_users u
    LEFT JOIN tw_follows f_in  ON f_in.followee_id  = u.id
    LEFT JOIN tw_follows f_out ON f_out.follower_id = u.id
    GROUP BY u.id, u.username, u.verified, u.follower_count, u.following_count
),
mutual_follows AS (
    SELECT
        a.follower_id AS user_id,
        COUNT(*)      AS mutual_count
    FROM tw_follows a
    JOIN tw_follows b
        ON a.follower_id = b.followee_id
       AND a.followee_id = b.follower_id
    GROUP BY a.follower_id
)
SELECT
    fs.username,
    fs.verified,
    fs.graph_followers,
    fs.graph_following,
    COALESCE(mf.mutual_count, 0)                        AS mutual_follows,
    -- Follower/following ratio: >1 means more people follow you than you follow
    ROUND(fs.graph_followers::numeric
          / NULLIF(fs.graph_following, 0), 2)           AS ff_ratio,
    -- Influence score: followers weighted by mutual engagement
    ROUND(
        fs.graph_followers * 1.0
        + COALESCE(mf.mutual_count, 0) * 2.0
        + CASE WHEN fs.verified THEN 500 ELSE 0 END
    )                                                   AS influence_score
FROM follow_stats fs
LEFT JOIN mutual_follows mf ON mf.user_id = fs.id
ORDER BY influence_score DESC
LIMIT 20;