Last Non-Null Value in Redshift by Group

1
votes

I am using Redshift and want to receive the last non-Null value by userid.

Here is an example dataset: 

     Date     UserID      Value
4-18-2018        abc          1
4-19-2018        abc       NULL
4-20-2018        abc       NULL
4-21-2018        abc          8
4-19-2018        def          9
4-20-2018        def         10
4-21-2018        def       NULL
4-22-2018        tey       NULL
4-23-2018        tey          2

If the new user starts out with a NULL then replace with 0.

I want my final dataset to look like this: 

     Date     UserID      Value
4-18-2018        abc          1
4-19-2018        abc          1
4-20-2018        abc          1
4-21-2018        abc          8
4-19-2018        def          9
4-20-2018        def         10
4-21-2018        def         10
4-22-2018        tey          1
4-23-2018        tey          2

Any help would be great thanks!

1
sqlnullamazon-redshiftlag

1 Answers

1
votes

You can do this with lag() and the ignore nulls option:

select date, userid,
       coalesce(value, lag(value ignore nulls) over (partition by userid order by date)) as value
from t;

If the values are increasing, you can also use a cumulative maximum:

select date, userid,
       max(value) over (partition by userid order by date) as value
from t;