Date range overlaps

date_ranges.sql

-- Calculate the number of days within a range overlapped by an arbitrary number of other ranges.

SELECT
  COUNT(active_day),
  action_id
FROM (
  SELECT
  DISTINCT(
    GENERATE_SERIES(
      start_at,
      finish_at,
      INTERVAL '1 day'
    )::date
  ) AS active_day,
  action_id
 FROM score_events
  WHERE (
    (start_at, finish_at) OVERLAPS (
      DATE('2011-01-01'),
      DATE('2011-01-06 06:00:01 UTC')
    )
  )
 ) AS active_day_series
 WHERE active_day BETWEEN '2011-01-01' AND '2011-01-06 06:00:01 UTC'
 GROUP BY action_id