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

How can this be done in SQL

For example, it can be

SELECT t1.id,
       t1.type,
       ( SELECT t2.type
         FROM sourcetable t2
         WHERE t2.id <= t1.id
           AND t2.type IS NOT NULL
         ORDER BY id DESC
         LIMIT 1 ) new_type
FROM sourcetable t1