title: Check LAG and LEAD until NULL when condition is met BigQuery
body: <p>DISCLAIMER: I apologize myself if the expression of this problem is too complicated. I tried my best.
  I had a really rough idea of how to solve this so I append the code down below although it doesn't contribute to solve my problem. Thank you.</p>
  <p>I have a table like this:</p>
  <p><a href="https://i.stack.imgur.com/kVjkr.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/kVjkr.png" alt="enter image description here" /></a></p>
  <pre><code>WITH table1 AS (
  
    SELECT  '2020-12-01 00:00:06 UTC' Datetime,   NULL Column1,  'A0' Column2, 'x' Column3 UNION ALL
    SELECT  '2020-12-01 00:00:16 UTC',           'A1',           'A0',         'x'  UNION ALL
    SELECT  '2020-12-01 00:00:26 UTC',           'A1',           'A1',         'y'  UNION ALL
    SELECT  '2020-12-01 00:00:36 UTC',           'A1',           'A1',         'y'  UNION ALL
    SELECT  '2020-12-01 00:00:46 UTC',           'A1',           'A2',         'z'  UNION ALL
    SELECT  '2020-12-01 00:00:56 UTC',           'A1',           'A2',         'z'  UNION ALL
    SELECT  '2020-12-01 00:00:66 UTC',           NULL,           'A2',         'z'  UNION ALL
    SELECT  '2020-12-01 00:01:06 UTC',           NULL,           'A2',         'z'   
  )  
  
  select * from table1
  </code></pre>
  <p>I want to create a new column and append each value from Column3 for the whole length of Column1 where NULLS are the boundaries if the condition (Column1 = Column2) has been met at least once.</p>
  <p>The detailed explanation:
  I want to first check for the condition Column1 = Column2; when this is true, I want to check the value in Column3. In my case this is 'y'.
  When the condition is met, I want to do LAG and LEAD until NULL in Column1 and then apply 'y' for the whole length of A1 in Column1 to the new column Column4. If the condition Column1 = Column2 would not be met in the first place, there should be NULL in Column4.</p>
  <p>The output table would then be:</p>
  <p><a href="https://i.stack.imgur.com/IX6Fq.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/IX6Fq.png" alt="enter image description here" /></a></p>
  <p>I tried with the code below but unfortunately the only thing it does is mess.</p>
  <pre><code>select *,
  case when lag(Column1) over (partition by Column1 = Column2 order by DateTime) is null
               then Column3
       when lead(Column1) over (partition by Column1 = Column2 order by DateTime) is null
               then Column3
               else NULL
          end as Column4
  from XX.YY.ZZ 
  order by datetime
  </code></pre>