Mention Networks & Hashtag Co-occurrence Analysis

Mentions reveal who talks to whom. Hashtag co-occurrence reveals which topics are discussed together — the building block of topic clustering and recommendation.

-- Mention network: who mentions whom the most?
SELECT
    u_from.username                                     AS from_user,
    u_to.username                                       AS to_user,
    COUNT(*)                                            AS mention_count,
    MIN(t.created_at)                                   AS first_mention,
    MAX(t.created_at)                                   AS last_mention,
    -- Is the mention relationship mutual?
    EXISTS (
        SELECT 1
        FROM tw_mentions m2
        JOIN tw_tweets t2 ON t2.id = m2.tweet_id
        WHERE t2.user_id = m.mentioned_user_id
          AND m2.mentioned_user_id = t.user_id
    )                                                   AS is_mutual_mention
FROM tw_mentions m
JOIN tw_tweets t     ON t.id = m.tweet_id
JOIN tw_users u_from ON u_from.id = t.user_id
JOIN tw_users u_to   ON u_to.id = m.mentioned_user_id
WHERE t.user_id != m.mentioned_user_id  -- exclude self-mentions
GROUP BY u_from.username, u_to.username, m.mentioned_user_id, t.user_id
HAVING COUNT(*) >= 2
ORDER BY mention_count DESC
LIMIT 20;