Find 2nd maximum start_date for a group of set_id in big query

Question

I have a table with data like below

I want the 2nd highest start date for each set where the status is C. e.g the answer should be

1 2015-05-01
2 2015-05-01
3 2015-06-01

Is there a simpler way to do this in bigquery?

Answer 1

First filter only records which have status = 'C', then use window functions to partition data by set_id and order by start_date inside each set. Then take 2nd value.

SELECT 
  set_id, 
  NTH_VALUE(start_date, 2) OVER(PARTITION BY set_id ORDER BY start_date DESC)
FROM table WHERE status = 'C'