title: Unable to calculate difference between CTE subquery outputs for use in larger PostgreSQL query output column
body: <p>Using PostgreSQL v9.4.5 from Shell I created a database called moments in <code>psql</code> by running <code>create database moments</code>. I then created a moments table:</p>
  
  <pre><code>CREATE TABLE moments
  (
    id SERIAL4 PRIMARY KEY,
    moment_type BIGINT NOT NULL,
    flag BIGINT NOT NULL,
    time TIMESTAMP NOT NULL,
    UNIQUE(moment_type, time)
  );
  INSERT INTO moments (moment_type, flag, time) VALUES (1, 7, '2016-10-29 12:00:00');
  INSERT INTO moments (moment_type, flag, time) VALUES (1, -30, '2016-10-29 13:00:00');
  INSERT INTO moments (moment_type, flag, time) VALUES (3, 5, '2016-10-29 14:00:00');
  INSERT INTO moments (moment_type, flag, time) VALUES (2, 9, '2016-10-29 18:00:00');
  INSERT INTO moments (moment_type, flag, time) VALUES (2, -20, '2016-10-29 17:00:00');
  INSERT INTO moments (moment_type, flag, time) VALUES (3, 10, '2016-10-29 16:00:00');
  </code></pre>
  
  <p>I run <code>select * from moments</code> to view the table:</p>
  
  <p><strong>Moments Table</strong></p>
  
  <pre><code> id | moment_type | flag |        time         
  ----+-------------+------+---------------------
    1 |           1 |    7 | 2016-10-29 12:00:00
    2 |           1 |  -30 | 2016-10-29 13:00:00
    3 |           3 |    5 | 2016-10-29 14:00:00
    4 |           2 |    9 | 2016-10-29 18:00:00
    5 |           2 |  -20 | 2016-10-29 17:00:00
    6 |           3 |   10 | 2016-10-29 16:00:00
  </code></pre>
  
  <p>I then try to write an SQL query that produces the following output, whereby for each pair of duplicate moment_type values it returns the difference between the flag value of the moment_type having the most recent timestamp value, and the flag value of the second most recent timestamp value, and lists the results in ascending order by moment_type.</p>
  
  <p><strong>Expected SQL Query Output</strong></p>
  
  <pre><code>moment_type | flag | 
  ------------+------+
            1 |  -37 |  (i.e. -30 - 7)
            2 |   29 |  (i.e.   9 - -20)
            3 |   5  |  (i.e.  10 - 5)
  </code></pre>
  
  <p>The SQL query that I came up with is as follows, which uses the WITH query to write multiple <a href="https://www.postgresql.org/docs/8.4/static/queries-with.html" rel="nofollow">Common Table Expressions (CET)</a> subqueries for use as temporary tables in the larger SELECT query at the end. I also use an <a href="https://www.postgresql.org/docs/9.1/static/xfunc-sql.html" rel="nofollow">SQL function</a> to calculate the difference between two of the subquery outputs (alternatively I think I could have just used <a href="https://msdn.microsoft.com/en-us/library/ms188753.aspx" rel="nofollow">DIFFERENCE</a> <code>DIFFERENCE(most_recent_flag, second_most_recent_flag) AS flag</code> instead of the function):</p>
  
  <pre><code>CREATE FUNCTION difference(most_recent_flag, second_most_recent_flag) RETURNS numeric AS $$
    SELECT $1 - $2;
  $$ LANGUAGE SQL;
  
  -- get two flags that have the most recent timestamps
  WITH two_most_recent_flags AS (
  SELECT moments.flag
  FROM moments
  ORDER BY moments.time DESC
  LIMIT 2
  ),
  -- get one flag that has the most recent timestamp
  most_recent_flag AS (
  SELECT *
  FROM two_most_recent_flags 
  ORDER BY flag DESC
  LIMIT 1
  ), 
  -- get one flag that has the second most recent timestamp
  second_most_recent_flag AS (
  SELECT *
  FROM two_most_recent_flags 
  ORDER BY flag ASC
  LIMIT 1
  )
  SELECT DISTINCT ON (moments.moment_type)
  moments.moment_type,
  difference(most_recent_flag, second_most_recent_flag) AS flag
  FROM moments
  ORDER BY moment_type ASC
  LIMIT 2;
  </code></pre>
  
  <p>But when I run the above SQL query in PostgreSQL, it returns the following error:</p>
  
  <pre><code>ERROR:  column "most_recent_flag" does not exist
  LINE 21: difference(most_recent_flag, second_most_recent_flag) AS fla...
  </code></pre>
  
  <p><strong>Question</strong></p>
  
  <p>What techniques can I use and how may I apply them to overcome this error, and calculate and display the differences in the <code>flag</code> column to achieve the <strong>Expected SQL Query Output</strong>? </p>
  
  <p>Note: Perhaps the <a href="https://www.postgresql.org/docs/current/static/tutorial-window.html" rel="nofollow">Window Function</a> may be used somehow as it performs calculations across table rows</p>