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:
with recursive thread as ( select id, in_reply_to_status_id, 0 as depth from tweets where in_reply_to_status_id is null union select tweets.id, tweets.in_reply_to_status_id, 1 + thread.depth as depth from thread join tweets on tweets.in_reply_to_status_id = thread.id) select * from thread order by depth desc
This uses a recursive CTE to sythensize a
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:
with recursive thread as ( select id, in_reply_to_status_id, 0 as depth, id as ids from tweets where in_reply_to_status_id is null union select tweets.id, tweets.in_reply_to_status_id, 1 + thread.depth as depth, thread.ids || ',' || tweets.id as ids from thread join tweets on tweets.in_reply_to_status_id = thread.id) select * from thread where depth > 1 order by depth asc
The results look like this:
Created 2023-01-30T09:59:20-08:00 · Edit