title: PostgreSQL Window Functions
body: <p>Consider the following table structure:</p>
  
  <pre><code>CREATE TABLE tb_log
  (
    id INTEGER PRIMARY KEY,
    moment DATE,
    old INTEGER,
    actual INTEGER
  );
  </code></pre>
  
  <p>Containing the data:</p>
  
  <pre><code>INSERT INTO
    tb_log ( id, moment, old, actual )
  VALUES
    ( 1, '2018-06-19', 10, 20 ),
    ( 2, '2018-06-21', 20, 30 ),
    ( 3, '2018-06-25', 30, 40 );
  </code></pre>
  
  <p>I'm trying to get from <code>tb_log</code> the period (start date and end date) at which a value was in effect.</p>
  
  <p><strong>Trial #1</strong> - Using <code>lag()</code> function:</p>
  
  <pre><code>SELECT
    lag( moment ) OVER (ORDER BY moment) date_start,
    moment AS date_end,
    old AS period_value
  FROM
     tb_log;
  </code></pre>
  
  <p>Which returns the following data:</p>
  
  <pre><code>| date_start |   date_end | period_value |
  |------------|------------|--------------|
  |     (null) | 2018-06-19 |           10 |
  | 2018-06-19 | 2018-06-21 |           20 |
  | 2018-06-21 | 2018-06-25 |           30 |
  </code></pre>
  
  <p><strong>Trial #2</strong> - Using <code>lead()</code> function:</p>
  
  <pre><code>SELECT
    moment AS date_start,
    lead( moment ) OVER (ORDER BY moment) date_end,
    actual AS period_value
  FROM
     tb_log;
  </code></pre>
  
  <p>Which returns the following data:</p>
  
  <pre><code>| date_start |   date_end | period_value |
  |------------|------------|--------------|
  | 2018-06-19 | 2018-06-21 |           20 |
  | 2018-06-21 | 2018-06-25 |           30 |
  | 2018-06-25 |     (null) |           40 |
  </code></pre>
  
  <p><a href="http://sqlfiddle.com/#!17/7a7ea/4" rel="noreferrer">SQLFiddle.com</a></p>
  
  <p>Is there any trick using <code>Window Functions</code> to return something like this:</p>
  
  <pre><code>| date_start |   date_end | period_value |
  |------------|------------|--------------|
  |     (null) | 2018-06-19 |           10 |
  | 2018-06-19 | 2018-06-21 |           20 |
  | 2018-06-21 | 2018-06-25 |           30 |
  | 2018-06-25 |     (null) |           40 |
  </code></pre>
  
  <p>Any Ideas?</p>