Reply Threads — Conversation Depth & Top Discussions

Reply chains form conversation trees. We use recursive CTEs to measure conversation depth, find the most-discussed tweets, and analyse which users drive the most conversation.

-- Reconstruct conversation threads and measure depth
WITH RECURSIVE thread AS (
    -- Anchor: root tweets (not a reply)
    SELECT
        id,
        user_id,
        reply_to_id,
        reply_count,
        created_at,
        0           AS depth,
        ARRAY[id]   AS path
    FROM tw_tweets
    WHERE reply_to_id IS NULL
      AND reply_count > 10

    UNION ALL

    SELECT
        t.id,
        t.user_id,
        t.reply_to_id,
        t.reply_count,
        t.created_at,
        th.depth + 1,
        th.path || t.id
    FROM tw_tweets t
    JOIN thread th ON th.id = t.reply_to_id
    WHERE t.id != ALL(th.path)
      AND th.depth < 8
)
SELECT
    path[1]                                             AS root_tweet_id,
    MAX(depth)                                          AS max_depth,
    COUNT(*)                                            AS thread_size,
    COUNT(DISTINCT user_id)                             AS unique_participants,
    MIN(created_at)                                     AS thread_started,
    MAX(created_at)                                     AS last_reply,
    ROUND(EXTRACT(EPOCH FROM
        MAX(created_at) - MIN(created_at)) / 3600, 1)  AS active_hours
FROM thread
GROUP BY path[1]
HAVING COUNT(*) > 3
ORDER BY thread_size DESC
LIMIT 15;