Common table expressions are a nice way to execute things inside an SQL statement, but only once. PostgreSQL will execute all the WITH clauses and allow us to use the results throughout the query.

Here is a simplified example of this:

test=# WITH x AS (SELECT avg(id) 
                  FROM generate_series(1, 10) AS id) 
   SELECT *, y - (SELECT avg FROM x) AS diff 
   FROM generate_series(1, 10) AS y 
   WHERE y > (SELECT avg FROM x); 
 y  | diff 
----+-------------------- 
  6 | 0.5000000000000000 
  7 | 1.5000000000000000 
  8 | 2.5000000000000000 
  9 | 3.5000000000000000 
 10 | 4.5000000000000000 
(5 rows) 

In this example, the WITH clause's common table extension (CTE) calculates the average value of the time series generated by the generate_series function. The resulting x can be used just like a table—all over the query. In my example, x is used twice.