Detect overlapping intervals generating ranges and usage rating. Contains a bunch of various code found online. Thank you.

detect_interval_overlaps.sql

WITH 
activities AS (
	SELECT * FROM (
	VALUES 
	('D', '2018-01-09 11:00:00'::timestamp, '2018-01-09 11:10:00'::timestamp, 1),
	('A', '2018-01-09 10:00:00'::timestamp, '2018-01-09 12:00:00'::timestamp, 1),
	('X', '2018-01-09 10:05:00'::timestamp, '2018-01-09 10:11:00'::timestamp, 1),
	('B', '2018-01-09 14:00:00'::timestamp, '2018-01-09 16:00:00'::timestamp, 1),
	('C', '2018-01-09 10:10:00'::timestamp, '2018-01-09 10:30:00'::timestamp, 1)
	) AS act ("name", "start", "end", "count")
)
, timeslots AS (
    SELECT
        tsrange(timepoint, lead(timepoint) OVER (ORDER BY timepoint)),lead(timepoint) OVER (ORDER BY timepoint)
    FROM (
        SELECT 
            unnest(ARRAY["start", "end"]) as timepoint
        FROM
            activities
        ORDER BY timepoint
    ) s
)
SELECT 
    GREATEST(MAX(start), lower(tsrange)),
    LEAST(MIN("end"), upper(tsrange)),
    array_agg(name),
    sum(count)
FROM 
    timeslots t
JOIN activities a
ON t.tsrange && tsrange(a.start, a.end)
GROUP BY tsrange
HAVING cardinality(array_agg(name)) = 1
--ORDER BY greatest ASC