Why should you care?

Analytics on PostgresVerse often needs “active customers this month” reused in three joins — a CTE defines it once.

See it live — copy this example

Run in pgAdmin or psql.

WITH active_customers AS (
  SELECT customer_id
  FROM orders
  WHERE created_at >= date_trunc('month', now())
  GROUP BY customer_id
  HAVING COUNT(*) >= 2
)
SELECT c.email, ac.customer_id
FROM active_customers ac
JOIN customers c ON c.customer_id = ac.customer_id;

What happened?

• The CTE finds customers with 2+ orders this month.
• The outer query joins to emails.
• Same statement, two logical steps — easier to read than nested subqueries.

Practice next

1. Populate orders and customers in PostgresVerse.
2. Run the CTE query alone — inspect the WITH block.
3. Add a second CTE referencing the first for average order value.
4. Add RECURSIVE for a category tree parent_id walk.
5. Use WITH ... INSERT to pipeline staging data in one statement.

Remember

WITH names a temporary result for one query. Multiple CTEs chain with commas. RECURSIVE CTEs walk trees and org charts.

PostgresVerse CRM export

Marketing exports repeat-buyer emails using active_customers CTE in a scheduled job.

Outcome: Analysts edit one readable block instead of three copy-pasted subqueries.