SQL - How to count rows between two values?

Question

I have an SQL table that contains timestamp and value associated. It looks like this :

ORIGINAL_TABLE:

timestamp	value
2022-06-03 00:09:15.000	75
2022-06-03 00:09:16.000	0
2022-06-03 00:09:19.000	0
2022-06-03 00:09:29.000	12
2022-06-03 00:09:44.000	0
2022-06-03 00:09:55.000	5
2022-06-03 00:09:56.000	0

I am trying to make a table like this one, where it only contains the rows where value==0 in the timestamp_start field. In the timestamp_end field, it contains the timestamp of the next value that is not equal to 0:

WANTED_TABLE :

timestamp_start	timestamp_end
2022-06-03 00:09:16.000	2022-06-03 00:09:29.000
2022-06-03 00:09:44.000	2022-06-03 00:09:55.000
2022-06-03 00:09:56.000	NULL

***EDIT *** For the last one, timestamp_end has to be empty (NULL) because there is 0 after.

I tried using

coalesce(lead(timeStamp,SELECT COUNT(*) 
FROM ORIGINAL_TABLE
WHERE value=0

However it only counts the number of rows where value == 0

Any tips ? Thanks!

Answer 1

I don't know what DB you are using. But I wrote this SQL query in PostgreSQL, I think that this query will be run in many Databases.

with org_table as materialized 
(
    select
        row_number() over (order by "timestamp") as r_num,
        "timestamp", 
        "value" 
    from original_table 
)
select min(a1.begintime) as begintime, a1.endtime from (
    select  
        t1."timestamp" as begintime, 
        min(t2."timestamp") as endtime
    from org_table t1 
    inner join org_table t2 on t2.r_num > t1.r_num and t2.value > 0
    where t1.value = 0 
    group by t1."timestamp"
) a1 
group by a1.endtime

Result:

begintime                   endtime
2022-06-03 00:09:16.000     2022-06-03 00:09:29.000
2022-06-03 00:09:44.000     2022-06-03 00:09:55.000