I’m looking to apply some logic that returns the distinct/longest date ranges in a particular table. In the example below, PKID 1 contains the date ranges in 2-4, then is proceeded by 5. I can’t think of a good way to return 1 and 5 without writing multiple MIN() and MAX() aggregates, comparing lengths of time, or writing a 10 line CASE statement with LAG() functions. Is there an easier way to return PKID 1 and 5 without doing 20 levels of logic tests?

I’ve found a few posts that come close to this problem but aren’t the same. I can’t think of a way to implement the OVERLAP function in this instance, but enjoyed learning about it.

Find overlapping date ranges in PostgreSQL

How to find overlapping date ranges in Postgresql between multiple rows?

Postgresql query between date ranges