Ladder function in BigQuery SQL

Question

In the example below, I would like to have kind of a sliding ladder function, with number 4 as a trigger threshold for incrementation

SELECT 
  t, 
  -- f(t) as u
FROM UNNEST([1, 2, 4, 1, 2, 3, 5, 1, 2, 3, 3]) as t


-- expected result
+---+---+
| t | u |
+---+---+
| 1 | 1 |
| 2 | 1 |
| 4 | 2 | -- increment u since 4 >= 4 has been found
| 1 | 2 |
| 2 | 2 |
| 3 | 2 |
| 5 | 3 | -- increment u since 5 >= 4 has been found
| 1 | 3 |
| 2 | 3 |
| 3 | 3 |
| 3 | 3 |
+---+---+

How can I implement f(t) to achieve that, maybe WINDOW functions ?

Thank you.

Answer 1

You can use UNNEST WITH OFFSET and COUNTIF() OVER():

SELECT 
  t, 
  1 + COUNTIF(t >= 4) OVER (ORDER BY offset) as u
FROM UNNEST([1, 2, 4, 1, 2, 3, 5, 1, 2, 3, 3]) as t WITH OFFSET as offset