Normalize multiple records into a single record looking at the enrollment period

I have a snowflake table which consists of students and their enrollment period, startdate and enddate as below:

I need to normalize this into one single row, where it says student enrollment starts on 2022-02-01 and ends on 2023-04-30, There should be one continuous enrollment to consider it as one row. One Break in between will create two rows likewise more breaks more rows.

I tried with Lag Lead Functions but couldn't figure it out. Any help would be appreciated.

I am not sure if it covers all use-cases but try this:

1. Create a test table and fill it with some test data:

CREATE OR REPLACE TABLE enrollment_intervals (studentid int, startdate date, enddate date);

INSERT INTO enrollment_intervals  
VALUES
    (1, '2023-01-01', '2023-04-30'),
    (1, '2022-12-01', '2022-12-31'),
    (1, '2022-11-01', '2022-11-30'),
    (1, '2022-02-01', '2022-10-31'),
    (1, '2022-10-01', '2022-10-31'),
    (2, '2023-01-01', '2023-02-01'),
    (3, '2024-01-01', '2024-05-01');

2. Actual query:

WITH ordered_intervals AS (
    SELECT studentid,
           startdate,
           enddate,
           LEAD(startdate) OVER (PARTITION BY studentid ORDER BY startdate) next_startdate
    FROM enrollment_intervals
    ORDER BY studentid, startdate
),
merged_intervals AS (
    SELECT studentid,
           startdate,
           COALESCE(
               (CASE WHEN enddate < DATEADD(day, -1, next_startdate) THEN enddate ELSE NULL END),
               MAX(enddate) OVER (PARTITION BY studentid ORDER BY startdate ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
           ) enddate
    FROM ordered_intervals
),
merged_intervals_with_lag AS (
    SELECT *,
           LAG(enddate) OVER (PARTITION BY studentid ORDER BY startdate) as prev_enddate
    FROM merged_intervals
),
grouped_intervals AS (
    SELECT *,
           SUM(CASE WHEN startdate > DATEADD(day, 1, prev_enddate) THEN 1 ELSE 0 END)
           OVER (PARTITION BY studentid ORDER BY startdate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as group_id
    FROM merged_intervals_with_lag
)
SELECT studentid,
       MIN(startdate) as continuous_start,
       MAX(enddate) as continuous_end
FROM grouped_intervals
GROUP BY studentid, group_id
ORDER BY studentid, continuous_start;

The idea here that you use CTE to merge intervals checking if there are no gaps and ensure that you are not missing overlaps and inclusions. I hope it helps.