WITH next as (
  SELECT
        user_id,
        start,
        end,
        COALESCE(DATETIME(LAG(end) over (partition by user_id order by start), '15 days') < start, 0) as step
    FROM
        t
),

num as (
  SELECT
      user_id,
      start,
      end,
      sum(step) over (partition by user_id order by start) + 1 as sub_sequence_num
  FROM
      next
)

SELECT
    user_id,
    start,
    end,
    sub_sequence_num,
    dense_rank() over (partition by user_id, sub_sequence_num order by start) as sub_num_in_seq
FROM
    num
ORDER BY
    user_id,
    start