CTEs & Window Functions

Build complex queries with Common Table Expressions, recursive queries, and window functions.

Overview

CTEs (Common Table Expressions) and window functions enable powerful analytical queries that would otherwise require multiple queries or complex subqueries.

Feature PostgreSQL MySQL SQLite MSSQL MongoDB
Non-recursive CTE ✅ ✅ 8.0+ ✅ ✅ ❌
Recursive CTE ✅ ✅ 8.0+ ✅ ✅ ❌
MATERIALIZED ✅ 12+ ❌ ❌ ❌ ❌
Window Functions ✅ ✅ 8.0+ ✅ ✅ ✅ 5.0+

Basic CTEs

CTEs create named temporary result sets that you can reference in the main query.

Multiple CTEs

Chain multiple CTEs together, with later CTEs able to reference earlier ones.

Recursive CTEs

Traverse hierarchical data like org charts, category trees, and graph structures.

Warning: Recursive CTEs can cause infinite loops without proper termination. Always include a stopping condition or use CYCLE detection (PostgreSQL 14+).

Materialized CTEs

Control whether PostgreSQL materializes CTE results or inlines them.

CTE Patterns

Pre-built patterns for common CTE use cases.

MongoDB Pipelines

MongoDB doesn't have CTEs, but $lookup and $graphLookup provide similar capabilities.

Window Functions

Perform calculations across rows without collapsing the result set.

Ranking Functions

  • • ROW_NUMBER() - Unique sequential number
  • • RANK() - Same rank for ties, gaps
  • • DENSE_RANK() - Same rank, no gaps
  • • NTILE(n) - Divide into n buckets

Value Functions

  • • LAG() - Previous row value
  • • LEAD() - Next row value
  • • FIRST_VALUE() - First in partition
  • • NTH_VALUE() - Nth in partition

Best Practices

Use CTEs for Readability

CTEs make complex queries more readable by breaking them into logical steps. Name CTEs descriptively to document their purpose.

Limit Recursive Depth

Always set a maximum depth for recursive queries. Include it in your termination condition (e.g., WHERE depth < 100).

Consider Performance

CTEs may be materialized (computed once) or inlined (recomputed each use). For complex CTEs referenced multiple times, check the query plan.

Use Window Functions Over Self-Joins

Window functions are often more efficient than self-joins for comparing rows within the same result set.