# 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.