'PostgreSQL Finding all rows that have a time difference of over 5 seconds, for each id

I have a table which consists of data like below:

id       status       timestamp
1        REGISTERED   1000
2        REGISTERED   1030
2        COMPLETED    1031
1        PROCESSING   1035
3        REGISTERED   1040
3        COMPLETED    1041
1        COMPLETED    1048
4        REGISTERED   1049
5        REGISTERED   1050
5        PROCESSING   1056
6        REGISTERED   1060
6        PROCESSING   1062
6        COMPLETED    1080

I need to have a query that finds all rows in the REGISTERED state, that has the next row for that id occur >=5 timestamp units later, or is straight up missing. For the above data, that would return these rows:

id       status       timestamp
1        REGISTERED   1000
4        REGISTERED   1049
5        REGISTERED   1050
sqlpostgresql


Solution 1:[1]

You can use lead

select id, status, timestamp
from (
    select id, status, timestamp
       , lead(timestamp) over(partition by id order by timestamp) nxt
    from mytable
) t 
where status = 'REGISTERED' and (nxt is null or timestamp < nxt - 5)

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1

Related Questions