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
);