Hard query for lib #39
Description
Please add many functions.
https://dbfiddle.uk/?rdbms=postgres_9.6&fiddle=04645a18d4d4082cf4cee40545473015
SELECT * FROM (
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
)s WHERE lead IS NOT NULL
)
SELECT
GREATEST(MAX(start), lower(tsrange)),
LEAST(MIN("end"), upper(tsrange)),
array_agg(id),
EXTRACT(EPOCH FROM (LEAST(MIN("end"), upper(tsrange)) - GREATEST(MAX(start), lower(tsrange))))
FROM
timeslots t
JOIN activities a
ON t.tsrange && tsrange(a.start, a.end)
GROUP BY tsrange
HAVING cardinality(array_agg(id)) > 1```