How to find most overlapping time period with date ranges

date, oracle, sql, time

Solution

Here's an example solution using a simple self-join and a `GROUP BY`:

WITH d(id, t1, t2) AS (
    SELECT 1, date '2010-01-01', date '2010-03-01' FROM DUAL UNION ALL
    SELECT 2, date '2010-02-01', date '2010-04-01' FROM DUAL UNION ALL
    SELECT 3, date '2010-02-01', date '2010-04-01' FROM DUAL UNION ALL
    SELECT 4, date '2010-01-01', date '2010-01-03' FROM DUAL UNION ALL
    SELECT 5, date '2011-01-01', date '2011-02-15' FROM DUAL
)
SELECT d1.id, d1.t1, d1.t2, 
       COUNT(*) "Overlap count", 
       LISTAGG('[' || d2.t1 || ', ' || d2.t2 || ']', ', ')
       WITHIN GROUP (ORDER BY d2.id) "Overlapping intervals"
FROM d d1 
LEFT OUTER JOIN d d2 
ON d2.t1 <= d1.t2 AND d1.t1 <= d2.t2
GROUP BY d1.id, d1.t1, d1.t2
ORDER BY COUNT(*) DESC

The `"Overlapping intervals"` aggregation is for illustration only.

SQLFiddle

... with output:

| ID | OVERLAP COUNT |                                                                          OVERLAPPING INTERVALS |
|----|---------------|------------------------------------------------------------------------------------------------|
|  1 |             4 | [01-JAN-10, 01-MAR-10], [01-FEB-10, 01-APR-10], [01-FEB-10, 01-APR-10], [01-JAN-10, 03-JAN-10] |
|  2 |             3 |                         [01-JAN-10, 01-MAR-10], [01-FEB-10, 01-APR-10], [01-FEB-10, 01-APR-10] |
|  3 |             3 |                         [01-JAN-10, 01-MAR-10], [01-FEB-10, 01-APR-10], [01-FEB-10, 01-APR-10] |
|  4 |             2 |                                                 [01-JAN-10, 01-MAR-10], [01-JAN-10, 03-JAN-10] |
|  5 |             1 |                                                                         [01-JAN-11, 15-FEB-11] |

Problem

Suppose you have a table with an identifier, a start time and an end time. These start and end times can be any length of time. The start time is always before the end time. Assume there are no nulls. What kind of query would tell me the most "popular" time, i.e. where the two ranges in each row overlaps with the most other rows? The real life application of this is that it's a table recording users' sign in and sign out times. I want to write a query that will tell me when the most concurrent users were logged in and see what period of time this was. Thank you.

Original source