Using recursive CTEs to explore hierarchical Twitter threads#

This TIL adapted from a Gist I put together in 2019, before I started tracking TILs here.

My twitter-to-sqlite tool produced a SQLite table with an in_reply_to_status column that referenced another tweet ID, for recording reply-to conversations.

I wanted to find the “deepest” tweets in my database - the tweets at the end of the longest reply-to thread.

I started by adapting this recipe by Robin Houston. Here’s the query I came up with:

1
with recursive thread as (
2
    select id, in_reply_to_status_id, 0 as depth
3
        from tweets
4
        where in_reply_to_status_id is null
5
    union
6
        select tweets.id, tweets.in_reply_to_status_id, 1 + thread.depth as depth
7
        from thread join tweets on tweets.in_reply_to_status_id = thread.id)
8
select * from thread order by depth desc

This uses a recursive CTE to sythensize a thread table.

The result I got looked like this (truncated):

Sure enough, tweet 1576674019239407616 is a reply to a VERY long Twitter thread I had created about Stable Diffusion.

Matthew Somerville suggested the following improvement, which returns the full path of tweet IDs leading to that tweet:

1
with recursive thread as (
2
    select id, in_reply_to_status_id, 0 as depth, id as ids
3
        from tweets
4
        where in_reply_to_status_id is null
5
    union
6
        select tweets.id, tweets.in_reply_to_status_id, 1 + thread.depth as depth, thread.ids || ',' || tweets.id as ids
7
        from thread join tweets on tweets.in_reply_to_status_id = thread.id)
8
select * from thread where depth > 1 order by depth asc

The results look like this: