SQL Gap Fill by ID

Question

I am attempting to gap fill in two scenarios. I can do it with one group but am uncertain with multiple

the data:

Order       ID      Amount
1       NULL        NULL
2       A           500
3       NULL        NULL
4       A           700
1       B           1000
2       NULL        NULL
3       NULL        NULL
4       B           1500

Target Result

Order   ID      Amount
1       A       500
2       A       500
3       A       700
4       A       700
1       B       1000
2       B       1500
3       B       1500
4       B       1500

user_5221944 · Answer

Consider below approach

select * except(amount),  
    first_value(amount ignore nulls) over win as amount
from (select distinct `order` from your_table where not `order` is null), 
(select distinct id from your_table where not id is null)
left join your_table using(`order`, id)
window win as (partition by id order by `order` rows between current row and unbounded following)

if applied to sample data in your question - output is

SQL Gap Fill by ID

1 Answers