The number of overlapping events SQL

Question

Hello everyone I'm trying to solve the following problem: let's say I have a table with

event_type	chat	time_start	time_end
A	chat 1	15:01:21	15:04:20
A	chat 2	15:00:13	15:03:10
A	chat 3	15:03:00	15:17:00
A	chat 4	14:50:00	15:03:05
B	chat 1	17:00:00	17:30:00

My goal is to output for each event_type and each chat the number of intersections with other chats. That is, I want the result to look like this

event_type	chat	Number of intersections
A	chat 1	4
A	chat 2	4
A	chat 3	4
A	chat 4	4
B	chat 1	0

I understand that I can use JON, but my real database is very large, and it does not load join's. So I thought of the following query

WITH T1
WITH Intervals AS (
    SELECT 
        time_start AS Event_time,
        1 AS cnt,
        event_type
    FROM 
        events
    UNION ALL
    SELECT 
        time_end AS Event_time,
        -1 AS cnt,
        event_type
    FROM 
        events
)
SELECT 
        event_type, chat,
        SUM(cnt) OVER (PARTITION BY event_type ORDER BY Event_time) AS Cumulative_count
 FROM 
        Intervals
 GROUP BY   event_type,chat.

But the result is not what I need. Does anyone have any idea how to solve this?

Answer 1

Assuming that you actually want:-

i.e. as per

My goal is to output for each event_type and each chat the number of intersections with other chats.

• "chat 1 type A" will intersect with "chat 2", "chat 3" and "chat 4" not itself and not chat 1 type B.
• etc
• "chat 1 type B" will intersect with nothing

then you could use:-

SELECT 
    event_type,
    chat,
    (
        SELECT count() 
        FROM events AS t2 
        WHERE
            t2.rowid <> t1.rowid
            AND
                (
                    t1.time_start BETWEEN t2.time_start AND t2.time_end
                    OR
                    t1.time_end BETWEEN t2.time_start AND t2.time_end
                )
    ) AS NOI
FROM events AS t1 
ORDER BY event_type,chat
;

• this does assume that the table is not a WITHOUT ROWID table, although you could use t2.event_type||t2.chat <> t1.event_type||t1.chat, instead of t2.rowid <> t1.rowid to achieve the same result.
• if you wanted the results as per your expected results i.e. to include the chat itself as one of the intersections but only if there are intersections then you could replace count() with count() + (count() > 0) and thus get:-