Window functions
A window function computes across a set of rows related to the current row without collapsing them: ROW_NUMBER, RANK, LAG/LEAD, and running totals with SUM(...) OVER (PARTITION BY ... ORDER BY ...). They replace most self-joins and correlated subqueries.
WITH monthly AS (
SELECT date_trunc('month', created_at) AS m,
customer_id,
SUM(total_cents) AS cents
FROM orders
GROUP BY 1, 2
)
SELECT m, customer_id, cents,
SUM(cents) OVER (PARTITION BY customer_id ORDER BY m) AS lifetime_cents,
RANK() OVER (PARTITION BY m ORDER BY cents DESC) AS rank_in_month
FROM monthly;CTEs and recursion
Common table expressions name intermediate results and make long queries readable; recursive CTEs walk hierarchies such as org charts or category trees. Always bound recursion depth to avoid runaway queries.
Indexes and plans
A B-tree index accelerates equality and range predicates and can serve ORDER BY; composite index column order matters and follows the leftmost-prefix rule. Wrapping an indexed column in a function usually disables the index. Read EXPLAIN ANALYZE for sequential scans on large tables, bad row estimates and spills to disk.