longest-threads.sql

create table tweet ( id serial, in_reply_to integer references tweet(id) );

insert into tweet (id, in_reply_to) values (1, NULL), (2, NULL), (3, 2), (4, 3), (5, 3), (6, 5);

with recursive thread as ( select id, in_reply_to, 0 as depth from tweet where in_reply_to is null union select tweet.id , tweet.in_reply_to , 1 + thread.depth as depth from thread join tweet on tweet.in_reply_to = thread.id ) select id from thread where depth = ( select max(depth) from thread );