mysql

How to count the number of groups of events that are more than 20 minutes from each other?

Consider the following events:

+---------------------+------------+
| S                   | D          |
+---------------------+------------+
| 2024-07-21 07:01:48 | 2024-07-21 |
| 2024-07-21 07:01:49 | 2024-07-21 |
| 2024-07-21 07:02:03 | 2024-07-21 |
| 2024-07-21 07:09:50 | 2024-07-21 |
| 2024-07-21 07:10:18 | 2024-07-21 |
| 2024-07-21 07:10:40 | 2024-07-21 |
| 2024-07-21 12:12:01 | 2024-07-21 |
| 2024-07-21 12:12:18 | 2024-07-21 |
| 2024-07-21 12:12:28 | 2024-07-21 |
| 2024-07-21 12:12:57 | 2024-07-21 |
| 2024-07-21 12:13:21 | 2024-07-21 |
| 2024-07-21 12:19:59 | 2024-07-21 |
| 2024-07-21 12:20:28 | 2024-07-21 |
| 2024-07-21 12:20:42 | 2024-07-21 |
| 2024-07-21 17:03:37 | 2024-07-21 |
| 2024-07-21 17:04:17 | 2024-07-21 |
| 2024-07-21 17:04:28 | 2024-07-21 |
| 2024-07-21 17:04:40 | 2024-07-21 |
| 2024-07-21 19:41:51 | 2024-07-21 |
| 2024-07-21 19:42:30 | 2024-07-21 |
| 2024-07-21 19:42:32 | 2024-07-21 |
| 2024-07-21 19:55:22 | 2024-07-21 |
| 2024-07-21 19:55:31 | 2024-07-21 |
| 2024-07-21 19:55:59 | 2024-07-21 |
| 2024-07-21 19:56:07 | 2024-07-21 |
| 2024-07-21 20:04:12 | 2024-07-21 |
| 2024-07-21 20:04:48 | 2024-07-21 |
| 2024-07-21 20:05:01 | 2024-07-21 |
| 2024-07-21 20:05:19 | 2024-07-21 |
| 2024-07-22 07:07:56 | 2024-07-22 |
| 2024-07-22 07:07:57 | 2024-07-22 |
| 2024-07-22 07:08:23 | 2024-07-22 |
| 2024-07-22 07:08:40 | 2024-07-22 |
| 2024-07-22 07:08:51 | 2024-07-22 |
| 2024-07-22 07:16:09 | 2024-07-22 |
| 2024-07-22 07:16:44 | 2024-07-22 |
| 2024-07-22 07:17:06 | 2024-07-22 |
| 2024-07-22 07:17:07 | 2024-07-22 |
| 2024-07-22 12:15:24 | 2024-07-22 |
| 2024-07-22 12:15:34 | 2024-07-22 |
| 2024-07-22 12:15:53 | 2024-07-22 |
| 2024-07-22 12:16:05 | 2024-07-22 |
| 2024-07-22 12:25:11 | 2024-07-22 |
| 2024-07-22 12:26:00 | 2024-07-22 |
| 2024-07-22 12:26:14 | 2024-07-22 |
| 2024-07-22 15:08:46 | 2024-07-22 |
| 2024-07-22 15:08:55 | 2024-07-22 |
| 2024-07-22 15:09:23 | 2024-07-22 |
| 2024-07-22 15:09:49 | 2024-07-22 |
| 2024-07-22 15:10:33 | 2024-07-22 |
| 2024-07-22 15:11:06 | 2024-07-22 |
| 2024-07-22 15:18:54 | 2024-07-22 |
| 2024-07-22 15:19:34 | 2024-07-22 |
| 2024-07-22 15:19:57 | 2024-07-22 |
| 2024-07-22 19:13:16 | 2024-07-22 |
| 2024-07-22 19:29:23 | 2024-07-22 |
| 2024-07-22 19:29:24 | 2024-07-22 |
| 2024-07-22 19:30:03 | 2024-07-22 |
| 2024-07-22 19:30:56 | 2024-07-22 |
| 2024-07-22 19:41:08 | 2024-07-22 |
| 2024-07-22 19:42:06 | 2024-07-22 |
| 2024-07-22 19:42:07 | 2024-07-22 |
| 2024-07-22 19:42:29 | 2024-07-22 |
| 2024-07-22 19:42:34 | 2024-07-22 |
+---------------------+------------+

I want to be able to query for the number of times that groups of events are more than 20 minutes apart from each other.

The above data should produce the following result:

+------------+-------+
| D          | COUNT |
+------------+-------+
| 2024-07-22 |     4 |
| 2024-07-21 |     4 |
+------------+-------+

I started a dbfiddle with the data, but I have no idea how to even start the query.

Solution

  • Use LAG()/LEAD() SQL function to compare with previous/next data.

    So, your answer should be:

    WITH T AS (
    select D, S, 
    LAG(S) OVER (PARTITION BY NULL order by S) as prev_s
    FROM Events
    order by S
)
SELECT D, 
    count(D) as count 
FROM T
where abs(TIME_TO_SEC(TIMEDIFF(S, prev_s))) > (60*20)
group by D
order by D DESC;

    enter image description here

    Update query in details dbfiddle