SQLite 3.39

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE test (id INTEGER NOT NULL, value STRING NOT NULL, successor_id INTEGER);
✓
INSERT INTO test (id, value, successor_id) VALUES 
( 1, 'v1',    2),
( 2, 'v2',    4),
( 4, 'v3', null),
( 7, 'v4',    9),
( 9, 'v5', null),
(12, 'v6', null);
✓
SELECT id, value, successor_id FROM test;


id
value
successor_id


1
v1
2

2
v2
4

4
v3
null

7
v4
9

9
v5
null

12
v6
null


WITH cte AS (
  SELECT *, LAG(successor_id) OVER (ORDER BY id) IS NULL flag
  FROM test
)
SELECT DISTINCT
       MIN(id) OVER (PARTITION BY grp) min_id,
       MAX(id) OVER (PARTITION BY grp) max_id,
       GROUP_CONCAT(value) OVER (
         PARTITION BY grp 
         ORDER BY id 
         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) "values" 
FROM (
  SELECT *, SUM(flag) OVER (ORDER BY id) grp
  FROM cte
);


min_id
max_id
values


1
4
v1,v2,v3

7
9
v4,v5

12
12
v6

id	value	successor_id
1	v1	2
2	v2	4
4	v3	null
7	v4	9
9	v5	null
12	v6	null