SQL — Window Functions & Query Plans

A SQL function that performs a calculation across a set of table rows related to the current row, without collapsing them into a single output row. Uses OVER() clause.

"SELECT name, salary, RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank FROM employees — ranks each employee within their department without removing any rows."

Clause within OVER() that divides the result set into partitions (groups) on which the window function is applied independently.

"PARTITION BY department means the running total resets for each department — each partition is its own independent window for the calculation."

Window function that assigns a sequential integer to each row within its partition, starting at 1. Unlike RANK(), it never produces ties.

"ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at) lets us number each purchase per user chronologically — useful for selecting only the first purchase."

Window functions that access the value of a column from a previous row (LAG) or next row (LEAD) within the same partition, without a self-join.

"LAG(amount, 1, 0) OVER (ORDER BY month) gives the previous month's amount — useful for calculating month-over-month change in a single query."

A temporary named result set defined with WITH, referenced within a SELECT, INSERT, UPDATE, or DELETE statement. Improves readability and can be recursive.

"WITH monthly_totals AS (SELECT DATE_TRUNC('month', sale_date) AS month, SUM(amount) AS total FROM sales GROUP BY 1) SELECT * FROM monthly_totals WHERE total > 100000 — the CTE names the subquery for clarity."

A CTE that references itself, consisting of an anchor member (base case) and a recursive member that builds on prior results. Used for hierarchical or graph-structured data.

"A recursive CTE traverses the employee org chart: anchor = CEO (no manager), recursive member = all employees managed by the previous result set, until no more rows are found."

A PostgreSQL command that executes a query and shows the actual execution plan with real timing and row count data — essential for query optimisation.

"EXPLAIN ANALYZE showed the query was doing a sequential scan on 2M rows instead of using the index — the planner ignored the index because the statistics were stale. Running ANALYZE fixed it."

A join that allows a subquery on the right side to reference columns from the left-side table, enabling row-by-row computations — similar to a correlated subquery but usable with set-returning functions.

"LATERAL lets us call a function once per row: SELECT u.id, recent.title FROM users u CROSS JOIN LATERAL (SELECT title FROM posts WHERE user_id = u.id ORDER BY created_at DESC LIMIT 3) AS recent."

A database object that stores the result of a query physically on disk, unlike a regular view. Must be refreshed to reflect changes in the underlying tables.

"REFRESH MATERIALIZED VIEW CONCURRENTLY summary_stats runs without locking reads — critical for our dashboard which queries the view thousands of times per minute."

Generalised Inverted Index — a PostgreSQL index type optimised for indexing composite values like arrays, JSONB, and full-text search vectors where a single value maps to multiple keys.

"A GIN index on the tags JSONB column makes WHERE tags @> '["postgres", "performance"]' instant even on 50M rows — without GIN, it would be a sequential scan."