Easy way to fill in last non-null value partition by user? Redshift SQL-CodePudding

I want to find a way to get the last non-null value for a purchase of a user. For example, I have this:

date        | user_id | purchase_amount
2020-01-01  | 1       | 39
2020-01-04  | 1       | null
2020-01-10  | 1       | 90
2020-01-15  | 1       | null

I want if to fill in the following way:

date        | user_id | purchase_amount
2020-01-01  | 1       | 39
2020-01-04  | 1       | 39
2020-01-10  | 1       | 90
2020-01-15  | 1       | 90

Right now, I am using a LEAD function partition by user_id but it still would consider even non-null values. How to approach this?

CodePudding user response：

As a disclaimer, this answer is largely based on the excellent

Demo

Note that the demo is for Postgres, but the code should also run on Redshift with no modification necessary.