SQL extract timeranges from one table based on join from other table

user_14215647 0
Viewed 51

Say, we have two tables:

log_table:

User_Id   Application   Log_TimeStamp
=======   ===========   =========
A         Alpha         2021-05-09T13:20:00
A         Alpha         2021-05-10T13:30:00
A         Alpha         2021-05-11T13:40:00
A         Alpha         2021-05-12T13:50:00
A         Alpha         2021-05-13T13:20:00
B         Beta          2021-05-14T13:30:00
B         Beta          2021-05-15T13:40:00
B         Beta          2021-05-16T13:50:00

confirmation_table:

User_Id   Application   Confirmation_TimeStamp
=======   ===========   =========
A         Alpha         2021-05-10T15:20:00
A         Alpha         2021-05-20T15:30:00
B         Beta          2021-05-21T13:40:00

Expected Result:

The result set should be a difference in time for each user and application from the log table which is before the next and after previous timestamp from the confirmation table . for the same user and application. See below:

User_Id   Application   TimeWindow
=======   ===========   =========
A         Alpha         (2021-05-10T13:30:00 - 2021-05-09T13:20:00) 
A         Alpha         (2021-05-13T13:20:00 - 2021-05-11T13:40:00)
B         Beta          (2021-05-16T13:50:00 - 2021-05-14T13:30:00)

Any suggestions on this?

I have tried to rank based on Log_TimeStamp < Confirmation_TimeStamp and grouping on user_if and application. But not sure how to get that window.

google-bigquery sql
1 Answers

Try this approach:

select c.*, l.ogtimestamp, l.next_logtimestamp
from confirmations c join
     (select l.*, lead(log_timestamp) as next_logtimestamp
      from logs l
     ) l
     on c.user_id = l.user_id and
        c.application = l.application and
        l.confirmation_timestamp >= l.log_timestamp and
        (l.confirmation_timestamp < l.next_logtimestamp or l.next_logtimestamp is null);
user_11440350
Related