Isolate/Sum Data Conditionally Based On Event Sequence

I have a table that tracks events sequentially and calculates the time for each event. What I want to do using the below sample data, is calculate the total number of Time Elapse (Seconds) of all Event A’s that happened before the second Event C occurred. So in the below example, I’d want an output of 550 seconds.

Obviously,

select sum(timeelapse_seconds) where eventtype = "A"

returns a result of 750 seconds because it includes event # 6.

Event EventType TimeElapse_Seconds
----------------------------------    
  1        C          50
  1        A         100
  2        A         100
  3        B         200
  4        A         350
  5        C         100
  6        A         200

Thanks!

Update

Apologies as I just realized something about my data set. There will be an initial Event 1 with EventType C. So, I need to find the second instance rather than the first (so min does not work). I’ve updated the sample table.

Answer

Below is for BigQuery Standard SQL

#standardSQL
SELECT SUM(TimeElapse_Seconds) TotalElapse_Seconds
FROM (
  SELECT EventType, TimeElapse_Seconds, 
    COUNTIF(EventType = 'C') OVER(ORDER BY Event) = 1 BeforeC
  FROM `project.dataset.table`
)
WHERE EventType = 'A' AND BeforeC

If to apply to sample data from your questin – result is

Row TotalElapse_Seconds  
1   550

Advertisement

Answer