SQL Query - list of record id's and record id groups

Given a simple table with a "record_id" and a "next_record_id", I need to output a list containing the "record_id" and a column "record_id_group".

Example table: record_id,next_record_id

1,2
2,3
3,4
6,7

The output should be:

1,1
2,1
3,1
4,1
6,6
7,6

The database is snowflake but I can convert from other formats with a working solution.

If your data is as "slim" as in the example

with r as(
  select  record_id groupN,record_id
  from test t
  where not exists (select 1 from test t2 where t.record_id=t2.next_record_id)
  union all
  select  r.groupN,t.next_record_id record_id
  from r inner join test t on t.record_id=r.record_id
)
select * from r
order by groupN,record_id

fiddle