CODEWITHSUNDEEP
sqlgoogle-bigquery
Fábio Perecin Foltram
Fábio Perecin Foltram

Reputation: 13

Trying to compare date in different rows in Bigquery

I am using Google Bigquery to compare date field on two different rows, like this:

Table

I would like evaluate the rows that have the same ID and are more than 30 minutes beetween them, but I am not able to compare two different rows beetwen them, and I coul even omit one of them, and count only the equal customerid rows that have more than 30 minutes beetween them...

Thank you very much.

Upvotes: 1

Views: 367

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

If you want to know if the customer accessed last more than 30 minutes before, use lag():

select t.*,
       (process_time_A >
        timestamp_add(lag(process_time_A) over (partition by customerId order by process_time_A), interval 30 minute)
       ) as is_more_than_30_minutes
from t

Upvotes: 1

rtenha
rtenha

Reputation: 3616

Consider a self join:

with joined as (
  select 
    a.*, 
    b.row as other_row,
    b.process_time_A as other_process_time, 
    timestamp_diff(a.process_time_A, b.process_time_A, day) as days_difference 
  from `table` a
  inner join `table` b using(customerID)
)
select * from joined
where days_difference >= 30

Upvotes: 0

Related Questions