LAG with condition

December 10, 2021

I want to get a value from the previous row that matches a certain condition.
For example: here I want for each row to get the timestamp from the last event = 1.
I feel I can do it without joins with LAG and PARTITION BY with CASE but I am not able to crack it.
Please help.

>Solution :

Here is one approach using analytic functions:

WITH cte AS (
    SELECT *, COUNT(CASE WHEN event = 1 THEN 1 END) OVER
                  (PARTITION BY customer_id ORDER BY ts) cnt
    FROM yourTable
)

SELECT ts, customer_id, event,
       MAX(CASE WHEN event = 1 THEN ts END) OVER
           (PARTITION BY customer_id, cnt) AS desired_result
FROM cte
ORDER BY customer_id, ts;

Demo

We can articulate your problem by saying that your want the desired_result column to contain the most recent timestamp value when the event was 1. The count (cnt) in the CTE above computes a pseudo group of records for each time the event is 1. Then we simply do a conditional aggregation over customer and pseudo group to find the timestamp value.