I have some data like the following in a Snowflake database

that's the result of a previous query. I'm trying to get the difference between message_created_at timestamps where the device_serial is the same between subsequent rows, and the first row (of the pair for the difference) has reason_code of 1 or 5, and the second row of the pair has reason_code 4.

For this example, my desired output would be

It's easy to calculate the time difference between every pair of rows (just lead or lag datediff). But I'm not sure how to structure a query to select only the desired rows so that I can get a datediff between them, without calculating spurious datediffs.

My ultimate goal is to see how these datediffs change between versions. I am but a lowly C programmer, my SQL-fu is weak.

CodePudding user response：

with data as (
    select *,
        count(case when reason_code in (1, 5) then 1 end)
            over (partition by device_serial order by message_created_at) as grp
        /* or alternately bracket by the end code */
        -- count(case when reason_code = 4 then 1 end)
            -- over (partition by device_serial order by message_created_at desc) as grp

    from T
)
select device_serial, min(version) as version,
    datediff(second, min(message_created_at), max(message_created_at)) as delta_seconds
from data
group by device_serial, grp