WITH RECURSIVE cte AS (
   SELECT id AS first, id AS last, 1 as len
   FROM   lines
   WHERE  previous_id IS NULL
   UNION ALL
   SELECT c.first, l.id, len + 1
   FROM   cte   c
   JOIN   lines l ON l.previous_id = c.last
   )
SELECT DISTINCT ON (first)
       last, len  -- , first -- also?
FROM   cte
ORDER  BY first, len DESC;

db<>在这里摆弄
精确生成结果。
如果你还想要第一个元素，比如你的标题状态，那是很容易得到的。

可以使用递归cte：

with recursive cte as (
      select l.previous_id as id, id as last
      from lines l
      where not exists (select 1 from lines l2 where l2.previous_id = l.id)
      union all
      select l.previous_id, cte.last
      from cte join
           lines l
           on cte.id = l.id
     )
select cte.last, count(*)
from cte
group by cte.last;

这是一把小提琴。

下面是microsoft sql server 2016 db中的一个实现

WITH chain
 AS (SELECT l.id AS [first], 
            l.id AS [last], 
            1 AS [len]
     FROM lines AS l
     WHERE l.previous_id IS NULL
     UNION ALL
     SELECT c.[first], 
            l.id, 
            c.[len] + 1 AS [len]
     FROM chain AS c
          JOIN lines AS l ON l.previous_id = c.[last]),
 result
 AS (SELECT DISTINCT 
            c.[first], 
            c.[last], 
            c.[len], 
            ROW_NUMBER() OVER(PARTITION BY c.[first] ORDER BY c.[len] DESC) AS rn
     FROM chain as c)
 SELECT r.[first], 
        r.[last], 
        r.[len]
 FROM result AS r
 WHERE r.rn = 1
 ORDER BY r.[first];