10 Mar /Common Table Expressions in Postgres

PostgresSQL WITH Queries, also known as Common Table Expressions (CTEs), allows us to write complex queries by defining temporary tables for use in a larger query. In other words, we can create in-memory temporary tables and query against them. Here is an (trivial) example where we want the email addresses of all the users who signed up on the biggest sales day.

WITH top_sales_by_date AS (
  SELECT date, SUM(order_total) AS total_sales
  FROM orders
  GROUP BY date
  ORDER BY total_sales DESC
)
SELECT email
FROM users
WHERE sign_up_date = (
  SELECT date
  FROM top_sales_by_date LIMIT 1
)

The top_sales_by_date is a table created just for this query that aggregates the order totals grouped by date, the ordered by total sales. We can use the date value from the top record in the temp table to find the emails of users who signed up on this date.