How to calculate time difference in a sql window partition using condition

Viewed 52

Problem

I have a table which lists series of events as "fetch" and click". User can perform multiple searches in 60 seconds. The tasks need to be sequenced so that user is performing a fetch and click within 60 seconds or multiple fetches within 60 seconds of each other and then clicking

  1. Fetch -> Click (user found the answer within 60 seconds )
  2. Fetch -> Fetch ->...Click (user didn't find the answer and kept searching until found the answer and then clicked within 60 seconds)
  3. Fetch -> no action (user didn't perform any further action after first fetch)

What I tried in the code below is to partition first on user_id, time as hour(hh) and time as minute(mm) to see the events that occurred within 60 seconds of each other but same hour and minute

I'm getting stuck at calculating how many clicks and fetches exist in a partition. I need to produce Output table by checking the conditions that

  1. If there is only 1 fetch and 1 click in a partition then flag as NA

  2. If (count(fetch) >=2 and count(click) = 1) and click is within 60 seconds of first fetch by the user then flag as Clicked and calculate the time between click and first fetch

  3. If there is only 1 fetch and no subsequent click, then flag it as Not-Clicked and calculate the time between this fetch and next fetch

Output table shows the desired data and columns.

Select a.window, a.user_id, a.search_id, a.date, a.time, a.action,

    count(case when a.action = 'click' then 1 else 0 end)  
       over(Partition BY  a.user_id, a.hh, a.mm ORDER BY  a.user_id asc, a.hh asc, a.mm asc) as total_clicks ,

    count(case when a.action = 'fetch' then 1 else 0 end) as total_fetches 
       over(Partition BY  a.user_id, a.hh, a.mm ORDER BY  a.user_id asc, a.hh asc, a.mm asc) as total_fetches 
From

(

    SELECT
        a.user_id, a.search_id, a.date, a.time, a.action, hour(a.time) as hh, minute(a.time) as mm,
        dense_rank() OVER ( ORDER BY  a.user_id asc, hour(a.time) asc, minute(a.time asc)) as window
     

    From input_table a

) as a

Input Table

window user_id search_id date time action
1 u1 s1 1/4/22 01/04/2022 10:05:10 fetch
1 u1 s1 1/4/22 01/04/2022 10:05:17 click
2 u1 s1 1/4/22 01/04/2022 10:16:02 fetch
2 u1 s2 1/4/22 01/04/2022 10:16:32 fetch
2 u1 s2 1/4/22 01/04/2022 10:16:37 click
5 u3 s7 1/4/22 01/04/2022 04:36:00 fetch
6 u3 s8 1/4/22 01/04/2022 04:37:00 fetch
6 u3 s8 1/4/22 01/04/2022 04:37:01 fetch
6 u3 s8 1/4/22 01/04/2022 04:37:04 click

Desired Output

window user_id search_id date time action flag clicked_time left_time
1 u1 s1 1/4/22 01/04/2022 10:05:10 fetch NA
1 u1 s1 1/4/22 01/04/2022 10:05:17 click NA
2 u1 s1 1/4/22 01/04/2022 10:16:02 fetch Clicked
2 u1 s2 1/4/22 01/04/2022 10:16:32 fetch Clicked
2 u1 s2 1/4/22 01/04/2022 10:16:37 click Clicked 35
5 u3 s7 1/4/22 01/04/2022 04:36:00 fetch Not-Clicked 60
6 u3 s8 1/4/22 01/04/2022 04:37:00 fetch Clicked
6 u3 s8 1/4/22 01/04/2022 04:37:01 fetch Clicked
6 u3 s8 1/4/22 01/04/2022 04:37:04 click Clicked 04
0 Answers
Related