Skip to content

Instantly share code, notes, and snippets.

@simonw simonw/deepest.sql
Created Oct 6, 2019

Embed
What would you like to do?
Query a twitter-to-sqlite database and find the "deepest" tweets in a reply thread, adapted from https://gist.github.com/robinhouston/f689a4b833dc027a3fd97e3de855927b
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
@simonw

This comment has been minimized.

Copy link
Owner Author

commented Oct 6, 2019

Variant using a suggestion from Matthew Somerville which adds the concatenated hierarchy of tweet IDs:

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

likes__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___________

@simonw

This comment has been minimized.

Copy link
Owner Author

commented Oct 6, 2019

Similar solution from Piers Cawley: https://twitter.com/pdcawley/status/1180898024832327680

WITH RECURSIVE threads(id, thread, depth) AS (
  SELECT tw.id, tw.id, 1 FROM tweets tw WHERE in_reply_to_status_id IS NULL
UNION
  SELECT tw.id, tw.id || ', ' || th.thread, th.depth + 1
    FROM threads th
    JOIN tweets tw ON th.id = tw.in_reply_to_status_id
)
select id, thread, depth from threads order by depth desc limit 10;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.