Common table expressions are a nice way to execute things inside an SQL statement, but only once. PostgreSQL will execute all 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.