Filter partitions in window - computing event recency in BigQuery

Question

Is there any way I can emulate the behavior of FILTER (http://modern-sql.com/feature/filter) in standard SQL BigQuery?

What I would need to do is:

SELECT MAX(date) FILTER (WHERE event_happend = 1) OVER ( PARTITION BY user_id ORDER BY date ASC ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING ) FROM ...

In essence I need to work out the most recent date a certain event occurred prior to the date of the current row. Column event_happened takes values 0 and 1 and I need the most recent date on which the event occurred (event_happened = 1) prior to the date of the current row.

Answer 1

Is there any way I can emulate the behavior of FILTER?

#standardSQL
SELECT
  MAX(IF(event_happend = 1, date, null))
  OVER (
    PARTITION BY user_id
    ORDER BY date ASC
    ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
  )
  FROM
    ...

Answer 2

Try this:

#standardSQL
WITH SampleData AS (
  SELECT 1 AS user_id, DATE '2017-11-02' AS date, 1 AS event_happend UNION ALL
  SELECT 1, DATE '2017-11-03', 0 UNION ALL
  SELECT 1, DATE '2017-11-04', 1 UNION ALL
  SELECT 1, DATE '2017-11-04', 1 UNION ALL
  SELECT 1, DATE '2017-11-05', 0 UNION ALL
  SELECT 2, DATE '2017-11-10', 1 UNION ALL
  SELECT 2, DATE '2017-11-11', 0 UNION ALL
  SELECT 2, DATE '2017-11-20', 0 UNION ALL
  SELECT 2, DATE '2017-11-21', 1
)
SELECT
  user_id,
  date,
  MAX(IF(event_happend = 1, date, NULL)) OVER (
    PARTITION BY user_id ORDER BY UNIX_DATE(date)
    RANGE BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
  ) AS max_date
FROM SampleData;

I included user_id and date to see what is going on. Note that using RANGE is important here--if you use ROWS, it may be the case that the previous row in the window has the same date value. By using RANGE with 1 PRECEDING, you can enforce that all rows in the window have a date value that is less than the current one.