Hashtag Trend Analysis — Velocity, Peaks & Emerging Topics

Trending hashtags are not just popular — they are accelerating. We measure tweet velocity (rate of growth), peak hour, and compare this week's volume to last week's to find emerging topics.

-- Hashtag leaderboard: volume, reach, and engagement
SELECT
    h.tag,
    COUNT(DISTINCT t.id)                                AS tweet_count,
    COUNT(DISTINCT t.user_id)                           AS unique_users,
    SUM(t.like_count)                                   AS total_likes,
    SUM(t.retweet_count)                                AS total_retweets,
    SUM(t.impression_count)                             AS total_impressions,
    ROUND(AVG(t.like_count), 1)                         AS avg_likes_per_tweet,
    MAX(t.like_count)                                   AS viral_tweet_likes,
    -- Engagement rate across all tweets using this hashtag
    ROUND(100.0 * SUM(t.like_count + t.retweet_count + t.reply_count)
          / NULLIF(SUM(t.impression_count), 0), 3)      AS hashtag_engagement_pct
FROM tw_hashtags h
JOIN tw_tweet_hashtags th ON th.hashtag_id = h.id
JOIN tw_tweets t          ON t.id = th.tweet_id
WHERE t.created_at >= NOW() - INTERVAL '30 days'
GROUP BY h.tag
ORDER BY tweet_count DESC;