subscriptions(user_id, started_at, ended_at) where ended_at is inclusive and never NULL.

For each user, merge the intervals that overlap or sit directly next to each other into continuous stretches of coverage. Directly next to each other means one ends on the day before the next begins, so 10 to 20 March and 21 to 30 March merge into one.

Return user_id, merged_start and merged_end, ordered by user then start.

The comparison has to be against the running maximum of the earlier end dates, not the previous row's end. One long subscription that swallows a short one would otherwise look like it had ended.

Tables

subscriptions

user_id  started_at  ended_at
-------  ----------  ----------
10       2026-01-01  2026-06-30
10       2026-03-01  2026-03-31
10       2026-04-01  2026-04-30
10       2026-09-01  2026-09-30
11       2026-03-10  2026-03-20
11       2026-03-21  2026-03-30
11       2026-04-01  2026-04-05

Expected result

user_id  merged_start  merged_end
-------  ------------  ----------
10       2026-01-01    2026-06-30
10       2026-09-01    2026-09-30
11       2026-03-10    2026-03-30
11       2026-04-01    2026-04-05