Compare array of datetime objects and pick all rows where difference between each and the next is less than 7 days

Question

My table looks like this:

(can't post images yet)

I want to select all names from my table where the time difference between each of the datetime objects and the next is always more than 7 days. So from the above I would get only Paul, since Adam's first two times are already only a day apart.

The best I can come up with is to get the time difference between the smallest and largest datetime in the array and then divide by array_length(datetime). So basically the average time all datetime objects, but that's not helping me.

I'm using Standard SQL on BigQuery

Answer 1

SELECT name
FROM dataset.table
WHERE NOT EXISTS(
  SELECT 1 FROM UNNEST(datetime) AS dt WITH OFFSET off
  WHERE DATETIME_DIFF(
    datetime[SAFE_OFFSET(off - 1)], dt, DAY
  ) <= 7
)

This compares each entry in the array with the one after it, looking for any where the number of days is 7 or less.