Postgresql group by overlapping date periods

scratch.sql

WITH period_ranges AS (
    SELECT 
        id, 
        tstzrange(period_start, period_end) AS period
    FROM 
        your_table
), recursive_group AS (
    SELECT 
        id,
        period,
        array[id] AS group_ids
    FROM 
        period_ranges
    UNION ALL
    SELECT 
        pr.id,
        pr.period,
        rg.group_ids || pr.id
    FROM 
        recursive_group rg
    JOIN 
        period_ranges pr ON rg.period && pr.period -- Check for overlap
    WHERE 
        NOT rg.group_ids @> array[pr.id] -- Ensure no duplicates
)
SELECT 
    DISTINCT ON (id)
    id,
    period,
    group_ids
FROM 
    recursive_group
ORDER BY 
    id, array_length(group_ids, 1) DESC;

Haven't tested it