Two periods overlap when each one starts before the other ends. That single condition catches every kind of overlap: partial, one inside the other, and identical.
The condition
For periods a and b:
a.start < b.end AND b.start < a.end
Check it with an example. January 1 to March 1 and February 1 to April 1 overlap: Jan 1 < Apr 1 and Feb 1 < Mar 1. Both true. January to February and March to April do not: the second start (March) is not before the first end (February), so false.
Query
SELECT a.user_id, a.sub_id AS sub_a, b.sub_id AS sub_b FROM subscriptions a JOIN subscriptions b ON a.user_id = b.user_id AND a.sub_id < b.sub_id -- each pair once, never a row against itself AND a.start_date < COALESCE(b.end_date, DATE '9999-12-31') AND b.start_date < COALESCE(a.end_date, DATE '9999-12-31');
a.sub_id < b.sub_id stops the same pair appearing twice (A with B, then B with A) and stops a row matching itself.
Details that cause bugs
- Inclusive or exclusive end date. If the end date is the last day of service (inclusive), a subscription ending on March 31 and a new one starting on March 31 do overlap. If end dates are exclusive (the next start date), they do not. Use
<=instead of<for inclusive. Ask which one the data uses. - Open-ended periods. An active subscription has
end_date IS NULL. The COALESCE above treats it as far in the future. Without it, the NULL comparison returns nothing and active overlaps are missed.
A window function version
For a large table the self-join can be heavy. Sort each user's periods by start, bring in the previous row's end with LAG, and flag a row where start_date < prev_end:
SELECT *
FROM (
SELECT *, MAX(COALESCE(end_date, DATE '9999-12-31'))
OVER (PARTITION BY user_id ORDER BY start_date
ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS prev_max_end
FROM subscriptions
) t
WHERE start_date < prev_max_end;Use the running maximum of earlier ends, not just the previous end. A long period can overlap a later one even if a shorter one sits in between.