SQL LAG() function with condition where

Question

I have my table like this:

    WITH 
my_table (user_id, date, event_name, value) AS (
    values 
    (1,'2021-10-01', 'level', 5),
    (1,'2021-10-03', 'purchase', null),
    (1,'2021-10-05', 'purchase', null),
    (2,'2021-10-02', 'level', 10),
    (2,'2021-10-03', 'purchase', null)
) 
SELECT *
FROM my_table

user_id	date	event_name	level
1	2021-10-01	level	5
1	2021-10-03	purchase
1	2021-10-05	purchase
2	2021-10-02	level	10
2	2021-10-03	purchase

All I want is to add column with the last level achieved before every purchase for every user_id. Desired table should look like this

user_id	date	event_name	level	level_achieved
1	2021-10-01	level	5
1	2021-10-03	purchase		5
1	2021-10-05	purchase		5
2	2021-10-02	level	10
2	2021-10-03	purchase		10

Thanks for helping me!

Answer 1

Assuming that level can't go down you can use just max which will ignore null's:

SELECT *,
    if(value is null, max(value) over(partition by user_id order by date)) level_achieved
FROM my_table;

Output:

user_id	date	event_name	value	level_achieved
1	2021-10-01	level	5	NULL
1	2021-10-03	purchase	NULL	5
1	2021-10-05	purchase	NULL	5
2	2021-10-02	level	10	NULL
2	2021-10-03	purchase	NULL	10

Or using last_value with ignore nulls option:

SELECT *,
    if(value is null, last_value(value) IGNORE NULLS over(partition by user_id order by date)) level_achieved
FROM my_table;