Is there a way to to identify the latest non null value in a window frame in BIGQUERY?

user_18811203 0
Viewed 128

I was hoping I could get some help on identifying the latest non-null value in a window frame ordered by date in BigQuery. I had some tries until now with first_value and partitions but no luck.

Input:

Date Key Status
2021-01-13 1 In Progress
2021-01-13 2 Closed
2021-01-14 1 Waiting
2021-01-14 2 NULL
2021-01-15 1 NULL
2021-01-15 2 NULL
2021-01-16 1 NULL
2021-01-16 2 NULL
2021-01-17 1 In Progress
2021-01-17 2 NULL
2021-01-18 1 NULL
2021-01-18 2 NULL

The expected output is:

Date Key Status
2021-01-13 1 In Progress
2021-01-13 2 Closed
2021-01-14 1 Waiting
2021-01-14 2 Closed
2021-01-15 1 Waiting
2021-01-15 2 Closed
2021-01-16 1 Waiting
2021-01-16 2 Closed
2021-01-17 1 In Progress
2021-01-17 2 Closed
2021-01-18 1 In Progress
2021-01-18 2 Closed

Hopefully, I have included the relevant information. Thank you in advance for your help

google-bigquery sql
1 Answers

Consider below approach

select * except(status),
  last_value(status ignore nulls) over(partition by key order by date) as status
from your_table

if applied to sample data in your question - output is

enter image description here

user_52219440
Related