snowflake-deep-cte-recursive.sql

with recursive hierarchy(id, id_prev, length) as
(
  -- only roots
  select id, id_prev, 1 as length
  from ecds_db.playground.orders
  where id_prev is null
  union all
  -- only joinable children
  select o.id, o.id_prev, p.length + 1
  from ecds_db.playground.orders o
  join hierarchy p on p.id = o.id_prev
)
select length, count(1)
from hierarchy
group by length
order by 1 desc