use `lag()` to get previous timestamp

Question

I am trying to get first step timestamp to seconds step to calculate how much time it took between two steps post:/login_request and post:/login using lag() but it's taking timestamp each step and add it next record.

*-----------------------------------------------------------------------------------------*
| device_serial  |  device_gen |   status_code |   method              |   event_time     |
*-----------------------------------------------------------------------------------------*
| ABC345         |  i13        |  200          |  post:/login_request  | 3/3/24 23:10:05  |
| ABC345         |  i13        |  200          |  post:/login          | 3/3/24 23:10:10  |
| EFG456         |  i13        |  200          |  post:/login_request  | 3/3/24 18:37:25  |
| EFG456         |  i13        |  200          |  post:/login          | 3/3/24 18:37:28  |
| EFG456         |  i13        |  200          |  post:/login_request  | 3/3/24 21:58:44  |
| EFG456         |  i13        |  200          |  post:/login          | 3/3/24 21:58:48  |                                                                       
*-----------------------------------------------------------------------------------------* 

My Query and fiddle:

select
    device_serial, 
    device_gen, 
    status_code, 
    method, 
    event_time,
    lag(event_time) over(partition by device_serial, status_code order by event_time) as first_step_ts
from test_tbl

what I am getting:

*-----------------------------------------------------------------------------------------------------------*
| device_serial  |  device_gen |   status_code |   method              |   event_time     | prev_ts         |
*-----------------------------------------------------------------------------------------------------------*
| ABC345         |  i13        |  200          |  post:/login_request  | 3/3/24 23:10:05  |                 |
| ABC345         |  i13        |  200          |  post:/login          | 3/3/24 23:10:10  | 3/3/24 23:10:05 |
| EFG456         |  i13        |  200          |  post:/login_request  | 3/3/24 18:37:25  |                 |
| EFG456         |  i13        |  200          |  post:/login          | 3/3/24 18:37:28  | 3/3/24 18:37:25 |
| EFG456         |  i13        |  200          |  post:/login_request  | 3/3/24 21:58:44  | 3/3/24 18:37:28 |
| EFG456         |  i13        |  200          |  post:/login          | 3/3/24 21:58:48  | 3/3/24 21:58:44 |
*-----------------------------------------------------------------------------------------------------------* 

what I actually want:

*-----------------------------------------------------------------------------------------------------------*
| device_serial  |  device_gen |   status_code |   method              |   event_time     | prev_ts         |
*-----------------------------------------------------------------------------------------------------------*
| ABC345         |  i13        |  200          |  post:/login_request  | 3/3/24 23:10:05  |                 |
| ABC345         |  i13        |  200          |  post:/login          | 3/3/24 23:10:10  | 3/3/24 23:10:05 |
| EFG456         |  i13        |  200          |  post:/login_request  | 3/3/24 18:37:25  |                 |
| EFG456         |  i13        |  200          |  post:/login          | 3/3/24 18:37:28  | 3/3/24 18:37:25 |
| EFG456         |  i13        |  200          |  post:/login_request  | 3/3/24 21:58:44  |                 |
| EFG456         |  i13        |  200          |  post:/login          | 3/3/24 21:58:48  | 3/3/24 21:58:44 |
*-----------------------------------------------------------------------------------------------------------* 

can anyone help to give me some guidane on how can I achieve above result.

Answer 1

You need to check methods if it is 'post:/login_request' and set the result to NULL

select
    device_serial, 
    device_gen, 
    status_code, 
    methods, 
    event_time,
  CASE WHEN  methods = 'post:/login_request' THEN NULL ELSE 
    lag(event_time) over(partition by device_serial, status_code order by event_time) END as first_step_ts
from test_tbl

device_serial	device_gen	status_code	methods	event_time	first_step_ts
ABC345	i13	200	post:/login_request	3/3/24 23:10:05	null
ABC345	i13	200	post:/login	3/3/24 23:10:10	3/3/24 23:10:05
EFG456	i13	200	post:/login_request	3/3/24 18:37:25	null
EFG456	i13	200	post:/login	3/3/24 18:37:28	3/3/24 18:37:25
EFG456	i13	200	post:/login_request	3/3/24 21:58:44	null
EFG456	i13	200	post:/login	3/3/24 21:58:48	3/3/24 21:58:44

SELECT 6

fiddle