title: Set all values in a column equal to the first non-null value over a window in Postgres
body: <p>Take this table as an example:</p>
  
  <pre><code>+----+------------+------+
  | id |    date    | flag |
  +----+------------+------+
  | A  | 01/01/2020 |    0 |
  | A  | 01/02/2020 |    0 |
  | A  | 01/03/2020 |    0 |
  | A  | 01/04/2020 |    1 |
  | A  | 01/05/2020 |    1 |
  | B  | 01/01/2020 |    0 |
  | B  | 01/02/2020 |    1 |
  | B  | 01/03/2020 |    1 |
  | B  | 01/04/2020 |    1 |
  | B  | 01/05/2020 |    1 |
  +----+------------+------+
  </code></pre>
  
  <p>There is some flag that is either set to 0 or 1. I want to create a new column called <code>day_flagged</code> that will contain the date that the flag first became a 1. For example, for <code>id</code> A, that would be <code>01/04/2020</code>. For <code>id</code> B, that would be <code>01/02/2020</code>.</p>
  
  <p>This is what I currently have:</p>
  
  <pre><code>SELECT x.id, 
         x.date, 
         ( CASE 
             WHEN prev_flag = 0 
                  AND next_flag = 1 
                  AND x.flag = 1 THEN 1 
             ELSE NULL 
           END ) AS flagged 
  FROM   (SELECT id, 
                 date, 
                 flag, 
                 Lag(flag) 
                   OVER ( 
                     partition BY id 
                     ORDER BY date ASC) AS prev_flag, 
                 Lead(flag) 
                   OVER ( 
                     partition BY id 
                     ORDER BY date ASC) AS next_flag 
          FROM   tableA) AS x;
  </code></pre>
  
  <p>The result of that is this:</p>
  
  <pre><code>+----+------------+---------+
  | id |    date    | flagged |
  +----+------------+---------+
  | A  | 01/01/2020 | null    |
  | A  | 01/02/2020 | null    |
  | A  | 01/03/2020 | null    |
  | A  | 01/04/2020 | 1       |
  | A  | 01/05/2020 | null    |
  | B  | 01/01/2020 | null    |
  | B  | 01/02/2020 | 1       |
  | B  | 01/03/2020 | null    |
  | B  | 01/04/2020 | null    |
  | B  | 01/05/2020 | null    |
  +----+------------+---------+
  </code></pre>
  
  <p>I'm able to identify when the value of <code>flag</code> for each <code>id</code> first changed from 0 to 1 and store that in <code>flagged</code>. How can I take the <code>date</code> value corresponding to the rows where <code>flagged</code> is 1, and insert that date into every row of the partition as <code>day_flagged</code>?</p>
  
  <p>Desired outcome:</p>
  
  <pre><code>+----+------------+------+-------------+
  | id |    date    | flag | day_flagged |
  +----+------------+------+-------------+
  | A  | 01/01/2020 |    0 | 01/04/2020  |
  | A  | 01/02/2020 |    0 | 01/04/2020  |
  | A  | 01/03/2020 |    0 | 01/04/2020  |
  | A  | 01/04/2020 |    1 | 01/04/2020  |
  | A  | 01/05/2020 |    1 | 01/04/2020  |
  | B  | 01/01/2020 |    0 | 01/02/2020  |
  | B  | 01/02/2020 |    1 | 01/02/2020  |
  | B  | 01/03/2020 |    1 | 01/02/2020  |
  | B  | 01/04/2020 |    1 | 01/02/2020  |
  | B  | 01/05/2020 |    1 | 01/02/2020  |
  +----+------------+------+-------------+
  </code></pre>
  
  <p>DB Fiddle: <a href="https://www.db-fiddle.com/f/wJsTnvNkYELHqLjHRx1pie/4" rel="nofollow noreferrer">https://www.db-fiddle.com/f/wJsTnvNkYELHqLjHRx1pie/4</a></p>