#BiqQuery #SQL back and forward fill

bq_sql_back_forward_fill.sql

with tt1 as (select 1 as time, null as val union all
      select 2, 1 union all
      select 3, null union all
      select 4, null union all
      select 5, null union all
      select 6, 0 union all
      select 7, null union all
      select 8, null
     )
select *
    , first_value(val ignore nulls) over (order by time) fv
    , last_value(val ignore nulls) over (order by time asc) as fill_down
    , last_value(val ignore nulls) over (order by time desc) as fill_down

    , first_value(val ignore nulls) over (order by time RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) fv2

from tt1
where true
order by time
;