How to create column based on previous rows?

Question

I have the following table:

id  type
1   NULL
2    A
3   NULL
4   NULL
5    A
6   NULL
7    B
8    A
9    B
10   NULL

I want to create a column where each row takes the current status if exist if not take the status from the previous one. Basically want to get this:

id  type   new_type
1   NULL    NULL   -- firs one no previous so it can only be null
2    A       A     -- current type A so take it
3   NULL     A     -- no type so take the new_type of previous
4   NULL     A
5    A       A
6   NULL     A
7    B       B
8    A       A
9    B       B
10   NULL    B

I know I need window function here but I don't know how a window function can reference a column that is "in progress" basically the window function need to reference both type and new_type but new_type doesn't exist yet.. it's the output.

How can this be done in SQL / Presto?

Answer 1

Presto has comprehensive support for window functions. Here, you can use lag() with the ignore nulls option to replace null values in column type:

select 
    id,
    type,
    coalesce(
        type,
        lag(type ignore nulls) over(order by id)
    ) new_type
from mytable