Alternative to CONDITIONAL_TRUE_EVENT in BigQuery, with a LAG() function?

Question

Vertica has a very nice type of operations: Event-Based Window operations, which basically let you identify when an event occurs. For example the CONDITIONAL_TRUE_EVENT will increment a counter each time the given boolean expression resolves to true.

Is there any way I can emulate this function using BigQuery? Please note that there is a LAG() function within the CONDITIONAL_TRUE_EVENT one.

Example:

CONDITIONAL_TRUE_EVENT(timestamp - LAG(timestamp) > '7 days')
OVER(PARTITION BY zuid, sub_type ORDER BY timestamp)

Thanks!

Answer 1

Below is for BigQuery

select zuid, sub_type, timestamp, 
  countif(flag) over(partition by zuid, sub_type order by timestamp) as conditional_true_event
from (
  select zuid, sub_type, timestamp,
    date(timestamp) - 7 > lag(date(timestamp)) over(partition by zuid, sub_type order by timestamp) flag
  from `project.dataset.table`
)
-- order by timestamp