Content Performance — What, When & How to Post

Data-driven content strategy: which tweet types perform best, what length maximises engagement, and which posting windows yield the highest reach.

-- Tweet type performance: original vs retweet vs reply
SELECT
    CASE
        WHEN retweet_of_id IS NOT NULL THEN 'Retweet'
        WHEN reply_to_id IS NOT NULL   THEN 'Reply'
        ELSE                                'Original'
    END                                                 AS tweet_type,
    COUNT(*)                                            AS tweet_count,
    ROUND(AVG(like_count), 1)                           AS avg_likes,
    ROUND(AVG(retweet_count), 1)                        AS avg_retweets,
    ROUND(AVG(impression_count))                        AS avg_impressions,
    ROUND(100.0 * SUM(like_count + retweet_count + reply_count)
          / NULLIF(SUM(impression_count), 0), 3)        AS engagement_rate_pct,
    ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP
        (ORDER BY like_count))                          AS median_likes,
    ROUND(PERCENTILE_CONT(0.95) WITHIN GROUP
        (ORDER BY like_count))                          AS p95_likes
FROM tw_tweets
WHERE created_at >= NOW() - INTERVAL '30 days'
GROUP BY 1
ORDER BY engagement_rate_pct DESC;