snowflake-deep-cte-4k.sql

with level1 as
(
  select id, id_prev, 1 as length
  from ecds_db.playground.orders
),
-- up to 4 links
level2 as
(
  select j1.id,
    case when j4.id is not null then j4.id_prev when j3.id is not null then j3.id_prev when j2.id is not null then j2.id_prev else j1.id_prev end as id_prev,
    j1.length + coalesce(j2.length, 0) + coalesce(j3.length, 0) + coalesce(j4.length, 0) as length
  from level1 j1
  left join level1 j2 on j1.id_prev = j2.id
  left join level1 j3 on j2.id_prev = j3.id
  left join level1 j4 on j3.id_prev = j4.id
),
-- up to 16 links
level3 as
(
  select j1.id,
    case when j4.id is not null then j4.id_prev when j3.id is not null then j3.id_prev when j2.id is not null then j2.id_prev else j1.id_prev end as id_prev,
    j1.length + coalesce(j2.length, 0) + coalesce(j3.length, 0) + coalesce(j4.length, 0) as length
  from level2 j1
  left join level2 j2 on j1.id_prev = j2.id
  left join level2 j3 on j2.id_prev = j3.id
  left join level2 j4 on j3.id_prev = j4.id
),
-- up to 64 links
level4 as
(
  select j1.id,
    case when j4.id is not null then j4.id_prev when j3.id is not null then j3.id_prev when j2.id is not null then j2.id_prev else j1.id_prev end as id_prev,
    j1.length + coalesce(j2.length, 0) + coalesce(j3.length, 0) + coalesce(j4.length, 0) as length
  from level3 j1
  left join level3 j2 on j1.id_prev = j2.id
  left join level3 j3 on j2.id_prev = j3.id
  left join level3 j4 on j3.id_prev = j4.id
),
-- up to 256 links
level5 as
(
  select j1.id,
    case when j4.id is not null then j4.id_prev when j3.id is not null then j3.id_prev when j2.id is not null then j2.id_prev else j1.id_prev end as id_prev,
    j1.length + coalesce(j2.length, 0) + coalesce(j3.length, 0) + coalesce(j4.length, 0) as length
  from level4 j1
  left join level4 j2 on j1.id_prev = j2.id
  left join level4 j3 on j2.id_prev = j3.id
  left join level4 j4 on j3.id_prev = j4.id
),
-- up to 1024 links
level6 as
(
  select j1.id,
    case when j4.id is not null then j4.id_prev when j3.id is not null then j3.id_prev when j2.id is not null then j2.id_prev else j1.id_prev end as id_prev,
    j1.length + coalesce(j2.length, 0) + coalesce(j3.length, 0) + coalesce(j4.length, 0) as length
  from level5 j1
  left join level5 j2 on j1.id_prev = j2.id
  left join level5 j3 on j2.id_prev = j3.id
  left join level5 j4 on j3.id_prev = j4.id
),
-- up to 4096 links
level7 as
(
  select j1.id,
    case when j4.id is not null then j4.id_prev when j3.id is not null then j3.id_prev when j2.id is not null then j2.id_prev else j1.id_prev end as id_prev,
    j1.length + coalesce(j2.length, 0) + coalesce(j3.length, 0) + coalesce(j4.length, 0) as length
  from level6 j1
  left join level6 j2 on j1.id_prev = j2.id
  left join level6 j3 on j2.id_prev = j3.id
  left join level6 j4 on j3.id_prev = j4.id
)
select length, count(1)
from level7
group by length
order by 1 desc