How to get the minimum value for a given time-period

user_5614011 0
Viewed 88

I have a table with equipment failure and resolved date. Until the failure is resolved, entries for each day will show as failed. Once the issue is resolved data will start from the next failure date. Below is an example

enter image description here

I want an output which will give me the first failure time for each resolved timestamp like enter image description here

I tried to do a left join between Resolved timestamp and failure dates AND take the min but that doesn't work.

google-bigquery sql
1 Answers

Consider below approach

select type, 
  max(timestamp) resolved_timestamp,
  min(timestamp) first_failure_timestamp
from (  
  select *, countif(status='resolved') over win as grp
  from your_table
  window win as (partition by type order by timestamp rows between unbounded preceding and 1 preceding)
)
group by type, grp

if applied to sample data in y our question - output is

enter image description here

user_52219440
Related