SQL Get row immediately before a particular column value

user_7637550 0
Viewed 25

I have a table like this

user | type |         time         | value
 1   |  a   | 2022-01-01 20:44:39  |  x1
 1   |  a   | 2022-01-01 20:44:45  |  x2
 1   |  b   | 2022-01-01 20:45:10  |  y1
 2   |  a   | 2022-02-13 09:44:39  |  x3
 2   |  b   | 2022-02-13 09:46:20  |  y2
 3   |  a   | 2022-01-01 20:44:39  |  x4
 4   |  a   | 2022-01-10 19:44:39  |  x5
 4   |  b   | 2022-01-10 19:48:39  |  y3

Now for every user when ordered by time, I want to get the sum of the values when the type is b and the value when the type is a immediately before that. So in the above case what I would want is:

user | sum   |       time
1    | x2+y1 | 2022-01-01 20:45:10
2    | x3+y2 | 2022-02-13 09:46:20
3    | null  | null
4    | x5+y3 | 2022-01-10 19:48:39

type b always comes after type a when ordered by time. For the users with no type b, the value should be null. Any help will be greatly appreciated.

sql
1 Answers

We can try using LEAD followed by an aggregation:

WITH cte AS (
    SELECT t.*, LEAD(value) OVER (PARTITION BY user ORDER BY time) lead_value,
                LEAD(type)  OVER (PARTITION BY user ORDER BY time) lead_type,
                LEAD(time)  OVER (PARTITION BY user ORDER BY time) lead_time
    FROM yourTable t
)

SELECT user,
       MAX(CASE WHEN type = 'a' AND lead_type = 'b'
                THEN value + lead_value END) AS sum,
       MAX(CASE WHEN type = 'a' AND lead_type = 'b'
                THEN lead_time END) AS lead_time
FROM cte
GROUP BY user
ORDER BY user;
user_18632290
Related