How do I count distinct events over sequential seconds?

I have a database with multiple columns, date, timestamp (HH:MM:SS), and phone numbers. I am trying to find a way to find unique calls in sequential seconds. For Example:

Date          Timestamp     Phone_number
10-12-2019    15:15:23      999-999-9999
10-12-2019    15:15:23      999-999-9999
10-12-2019    15:15:24      999-999-9999
10-12-2019    15:15:24      999-999-9999
10-12-2019    15:15:25      999-999-9999
10-12-2019    15:20:21      111-111-1111
10-12-2019    15:20:21      111-111-1111
10-12-2019    15:20:22      111-111-1111
10-12-2019    15:22:33      999-999-9999

Ideally, I’d like to count the first 999-999-9999 as one call and 111-111-1111 as one call, and the second 999-999-9999 call as a call on the basis of the calls have the same timestamp or occurred in sequential seconds.

With an output similar to this with a count of 1 with the min(timestamp) of the non sequential call:

Date          Timestamp     Phone_number
10-12-2019    15:15:23      999-999-9999
10-12-2019    15:20:21      111-111-1111
10-12-2019    15:22:33      999-999-9999

I’m not entirely sure where to start. I believe I’d have to create a subselect of the time based on the previous second. Not sure how to implement it…or if it would hand timestamps with the same second

SELECT t1.time, t1.phone_number, COUNT(DISTINCT t2.x)
FROM myTable AS t1
JOIN myTable AS t2 ON t2.timestamp BETWEEN DATE_SUB(t1.timestamp, INTERVAL 1 second) AND t1.timestamp
GROUP BY t1.timestamp, t1.phone_number

I’m using MySql. Open to any help! Thank you in advance

Answer

In MySQL 8.0, this can be solved using window function lag(). You can recover the date/time of the previous call to the same number in a subquery, and then filter out records for which a previous call to the same number occured one second before:

select date, timestamp, phone_number
from (
    select 
        t.*,
        lag(concat(date, ' ', timestamp)) 
            over(partition by phone_number order by date, timestamp) lag_datetime
    from mytable t
) x
where 
    lag_datetime is null
    or concat(date, ' ', timestamp) > lag_datetime + interval 1 second

Note: storing date and time in separated columns is not a good idea, since it complicates the logic when you need to actually compare date/times.

Demo on DB Fiddle:

| date       | timestamp | phone_number |
| ---------- | --------- | ------------ |
| 2019-10-12 | 15:15:23  | 999-999-9999 |
| 2019-10-12 | 15:20:21  | 111-111-1111 |
| 2019-10-12 | 15:22:33  | 999-999-9999 |