Question 223187 in Amazon Web Sevices Question posted in
The official Amazon Web Services documentation can be found here.

Amazon web services – 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
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!

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search