Analytics Engineering, Bigquery, Sql, Data Engineering, Editors Pick.
want: This is the same query as in our first (incorrect) solution. But the inputs are different, so the output looks much better. Image by author. The window function last_value(… ignore nulls) allows us to fill in the blanks, and the window function lead lets us retrieve the end_dates to re-form