Skip to content
Advertisement

Count rows between time periods excluding time-overlap

I have a table where it holds some vehicle events, I need to count the number of event occurrences every 5 minutes.

Sample :

I was able to get the results by the following query :

query results :

The issue with the above results is that the query excluded Ids (15 and 28) the correct output should be :

SQLFiddle

your help would be much appreciated.

Advertisement

Answer

I believe this can only be done via recursive cte because the 5 minute window can contain arbitrary number of rows… a situation where lead/lag/rows between x preceding will not work. Here is my stab at it:

Demo on db<>fiddle

User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement