Full Twitter Analytics Dashboard — Multi-Signal Summary

A single query that gives a complete picture of platform health: activity, engagement quality, top content, trending topics, and network growth — the kind of report a social media analytics team would ship weekly.

-- Weekly Twitter health dashboard
WITH
date_range AS (
    SELECT
        NOW() - INTERVAL '7 days'  AS period_start,
        NOW()                      AS period_end,
        NOW() - INTERVAL '14 days' AS prev_start,
        NOW() - INTERVAL '7 days'  AS prev_end
),
-- 1. Activity metrics
activity AS (
    SELECT
        COUNT(*) FILTER (WHERE created_at >= d.period_start)          AS tweets_this_week,
        COUNT(*) FILTER (WHERE created_at BETWEEN d.prev_start AND d.prev_end)
                                                                       AS tweets_last_week,
        COUNT(DISTINCT user_id) FILTER (WHERE created_at >= d.period_start)
                                                                       AS dau_approx,
        COUNT(*) FILTER (WHERE created_at >= d.period_start
                           AND retweet_of_id IS NULL)                  AS original_this_week,
        COUNT(*) FILTER (WHERE created_at >= d.period_start
                           AND retweet_of_id IS NOT NULL)              AS retweets_this_week
    FROM tw_tweets, date_range d
),
-- 2. Engagement totals this week
engagement AS (
    SELECT
        SUM(like_count) FILTER (WHERE created_at >= (SELECT period_start FROM date_range))
                                                                       AS total_likes,
        SUM(retweet_count) FILTER (WHERE created_at >= (SELECT period_start FROM date_range))
                                                                       AS total_retweets,
        SUM(impression_count) FILTER (WHERE created_at >= (SELECT period_start FROM date_range))
                                                                       AS total_impressions,
        ROUND(AVG(like_count) FILTER (
            WHERE created_at >= (SELECT period_start FROM date_range)
              AND retweet_of_id IS NULL), 1)                           AS avg_likes_original
    FROM tw_tweets
),
-- 3. Top tweet this week
top_tweet AS (
    SELECT DISTINCT ON (1)
        1 AS grp,
        u.username,
        LEFT(t.content, 60)                                            AS preview,
        t.like_count,
        t.retweet_count
    FROM tw_tweets t
    JOIN tw_users u ON u.id = t.user_id
    WHERE t.created_at >= (SELECT period_start FROM date_range)
    ORDER BY 1, t.like_count DESC
),
-- 4. Top hashtag this week
top_hashtag AS (
    SELECT DISTINCT ON (1)
        1 AS grp,
        h.tag,
        COUNT(*) OVER (PARTITION BY h.id)                              AS tweet_count
    FROM tw_tweet_hashtags th
    JOIN tw_hashtags h ON h.id = th.hashtag_id
    JOIN tw_tweets t   ON t.id = th.tweet_id
    WHERE t.created_at >= (SELECT period_start FROM date_range)
    ORDER BY 1, COUNT(*) OVER (PARTITION BY h.id) DESC
),
-- 5. New follows this week
network_growth AS (
    SELECT COUNT(*) AS new_follows
    FROM tw_follows
    WHERE followed_at >= (SELECT period_start FROM date_range)
)
SELECT
    -- Activity
    a.tweets_this_week,
    a.tweets_last_week,
    ROUND(100.0 * (a.tweets_this_week - a.tweets_last_week)
          / NULLIF(a.tweets_last_week, 0), 1)                          AS tweet_wow_pct,
    a.dau_approx,
    a.original_this_week,
    a.retweets_this_week,
    ROUND(100.0 * a.original_this_week
          / NULLIF(a.tweets_this_week, 0), 1)                          AS originality_pct,
    -- Engagement
    e.total_likes,
    e.total_retweets,
    e.total_impressions,
    e.avg_likes_original,
    ROUND(100.0 * (e.total_likes + e.total_retweets)
          / NULLIF(e.total_impressions, 0), 3)                         AS platform_engagement_rate,
    -- Top content
    tt.username                                                        AS top_user,
    tt.preview                                                         AS top_tweet_preview,
    tt.like_count                                                      AS top_tweet_likes,
    th.tag                                                             AS top_hashtag,
    -- Network
    ng.new_follows
FROM activity a
CROSS JOIN engagement e
CROSS JOIN top_tweet tt
CROSS JOIN top_hashtag th
CROSS JOIN network_growth ng;