title: LAG(offset) until value is reached in BigQuery
body: <p>Is it somehow possible to use <code>LAG()</code> in BigQuery in such a way that it continues until a certain value or condition is met? In other words, I need the <code>offset</code> to be dynamic based on the number of rows to jump.</p>
  
  <p>For example:</p>
  
  <pre><code>#standardSQL
  WITH input AS (
    SELECT 1 AS id, 1 AS col_1, 'A' AS col_2 UNION ALL
    SELECT 2 AS id, 0 AS col_1, 'B' AS col_2 UNION ALL
    SELECT 3 AS id, 0 AS col_1, 'C' AS col_2 UNION ALL
    SELECT 4 AS id, 1 AS col_1, 'D' AS col_2 UNION ALL
    SELECT 5 AS id, 1 AS col_1, 'E' AS col_2 UNION ALL
    SELECT 6 AS id, 0 AS col_1, 'F' AS col_2 UNION ALL
    SELECT 7 AS id, 1 AS col_1, 'G' AS col_2 )
  </code></pre>
  
  <p>If <code>col_1</code> equals 0, then take the value of <code>col_2</code> where the <strong>last</strong> preceding <code>col_1</code> equals 1.</p>
  
  <p>Desired output:</p>
  
  <p><a href="https://i.stack.imgur.com/1ftrs.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/1ftrs.png" alt="enter image description here"></a></p>
  
  <p>I could do it with self joins, but I'd rather avoid using joins if possible i.e. there has to be a smarter way!</p>