Vol. I · A Postgres Handbook

The Query & the Schema

A reading-room reference for writing PostgreSQL by hand — selects to window functions, with the schema in plain view.

03

Chapter 3

CTEs & Recursive

Common table expressions. Named building blocks, recursion, and the readable alternative to nested subqueries.

In this chapter

15

queries, easy → advanced

  1. 001Basic CTE: average salary with department namesRead
  2. 002Multiple CTEs chained togetherRead
  3. 003CTE for deduplication using ROW_NUMBERRead
  4. 004CTE in UPDATE statementRead
  5. 005CTE in DELETE: remove old ordersRead
  6. 006Recursive CTE: generate a date seriesRead
  7. 007Recursive CTE: category tree with depthRead
  8. 008CTE with MATERIALIZED hint for performanceRead
  9. 009CTE to simplify complex query readabilityRead
  10. 010CTE for top customer per quarterRead
  11. 011Recursive CTE: calculate compound interest over yearsRead
  12. 012CTE to detect and remove duplicate rowsRead
  13. 013Full employee performance scorecardRead
  14. 014Classic: find all customers with no orders (multiple methods)Read
  15. 015CTE: calculate running percentile by dateRead