Preceding row without impact of WHERE clause

Agenobarb 0
Viewed 38

I need to get a preceding row even if doesn't match conditions in WHERE. Here is my data:

enter image description here

I need to get the request_id of the row preceding the row with flag=true In example above it would be the one at 2022-09-10 07:02:04 (please disregard the hilghlight, I didn't notice there is earlier one)

I can use LAG:

SELECT LAG(request_id, 1) OVER (PARTITION BY session_id ORDER BY time) 
FROM table
WHERE flag=true

But flag=true conditon will apply to LAG function too.

Is there a way to do it?

presto sql
1 Answers
WITH CTE as (
SELECT time
     ,CASE WHEN flag=true THEN LAG(request_id, 1) OVER (PARTITION BY session_id ORDER BY time)  END as prev_request_id
FROM table
)

select time,  prev_request_id
from CTE
  WHERE prev_request_id is not null

Figured I can do it with CASE but not sure if it is efficient way doing it. The flag is quite rare and I think it would be good to filter out requests that don't have it

Agenobarb0
Related