SQL LAG() function with condition where

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

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

Thanks for helping me!

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:

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;

I would advise you to create a subquery with the following:

user_id, level, date as date_start, lead(date) over(partition by user_id order by date asc) as date_end
(where level is not null)

You will get a structure like:

user_id, level, date_start, date_end

Next you can join with:

        my_table ON my_table.date >= sub_query.date_start 
    AND my_table.date < sub_query.date_end
AND my_table.user_id = sub_query.user_id

With this you can easily get the level for that specific date range.

Good luck