SQL BigQuery function to return the previous non null value

Question

I have the following table where some of the values are null and I would like to fill them with the previous non-null values.

LAG function helps but if there are more than one null value in a row, it does not work. *

What other approaches could be used?

Answer 1

You can use BigQuery window functions for that. For example:

select cohort_date, activity_day, 
       last_value(revenue_real ignore nulls) over (
                   partition by cohort_date
                   order by activity_day 
                   rows between unbounded preceding and current row
       ) revenue_real_filled 
from your_dataset.your_table