Window Functions

Row-level calculations across a related set of rows — without collapsing the result

Why

Window Functions are the single biggest leap in SQL capability. They let you compute rankings, running totals, moving averages, and comparisons to previous rows — all while keeping every individual row in the output. The same calculations without window functions require self-joins or correlated subqueries that are harder to read and often much slower.

Intuition

A window function adds a column to each row based on a calculation over a "window" of related rows — without collapsing rows the way GROUP BY does. The OVER() clause defines the window: PARTITION BY resets the window per group (like GROUP BY but without collapsing), and ORDER BY controls which rows are "before" the current one for running calculations.

Think of the window as a sliding frame that the database drags over each row in turn, computing the function over the visible rows in that frame.

Explanation

Ranking functions

SELECT name, department, salary, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num, RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk, DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rnk FROM employees; -- ROW_NUMBER: unique sequential number, no ties (1, 2, 3, 4) -- RANK: ties share the same rank, gaps after a tie (1, 2, 2, 4) -- DENSE_RANK: ties share the same rank, no gaps (1, 2, 2, 3) -- Classic pattern: top-N per group (e.g., top 3 earners per dept) SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn FROM employees ) ranked WHERE rn <= 3;

Aggregate and offset functions

-- Running total and moving average SELECT order_date, amount, SUM(amount) OVER (ORDER BY order_date) AS running_total, AVG(amount) OVER (ORDER BY order_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg_7 FROM orders; -- LAG / LEAD — access previous or next row's value SELECT month, revenue, LAG(revenue, 1) OVER (ORDER BY month) AS prev_month, LEAD(revenue, 1) OVER (ORDER BY month) AS next_month, revenue - LAG(revenue, 1) OVER (ORDER BY month) AS mom_change FROM monthly_revenue; -- FIRST_VALUE / LAST_VALUE SELECT name, salary, FIRST_VALUE(salary) OVER (PARTITION BY department ORDER BY salary DESC) AS dept_max FROM employees;

LAG and LEAD are the idiomatic way to compare a row to its predecessor or successor — no self-join needed. The ROWS BETWEEN frame clause controls exactly which rows are in the window for running calculations.

Subqueries & CTEs

Building complex queries in readable layers — and when to use each approach

Why

Real-world SQL questions require multiple steps — filter, aggregate, rank, then filter again. Subqueries and CTEs let you name intermediate results and build queries in layers. CTEs in particular transform deeply nested unreadable SQL into a step-by-step structure that reads almost like code comments, and are preferred over subquery nesting for readability.

Intuition

A subquery is a query embedded inside another query — either in FROM (derived table), WHERE (scalar or list subquery), or SELECT (correlated subquery). A CTE (Common Table Expression) is like giving a subquery a name at the top of the query. They're often equivalent in what they compute, but CTEs are almost always more readable.

Think of CTEs as named intermediate steps you build up, left to right, and then compose in the final SELECT — like variable assignment in a programming language.

Explanation

Subquery types

-- Scalar subquery — returns one value, usable anywhere SELECT name, salary, (SELECT AVG(salary) FROM employees) AS company_avg FROM employees; -- Subquery in FROM (derived table) — treated as a temporary table SELECT dept, avg_sal FROM ( SELECT department AS dept, AVG(salary) AS avg_sal FROM employees GROUP BY department ) dept_avgs WHERE avg_sal > 90000; -- Subquery in WHERE — filter using a list SELECT name FROM employees WHERE department_id IN ( SELECT id FROM departments WHERE location = 'NYC' ); -- Correlated subquery — references the outer query (runs per row) SELECT name, salary FROM employees e WHERE salary > ( SELECT AVG(salary) FROM employees WHERE department = e.department -- references outer row );

Correlated subqueries run once per outer row — they can be very slow on large tables. Rewrite them as a join or CTE with a pre-computed aggregate when performance matters.

CTEs with WITH

-- CTE: name intermediate results at the top, use below WITH dept_stats AS ( SELECT department, AVG(salary) AS avg_sal, COUNT(*) AS headcount FROM employees GROUP BY department ), top_depts AS ( SELECT department FROM dept_stats WHERE avg_sal > 100000 AND headcount >= 5 ) SELECT e.name, e.salary, e.department FROM employees e JOIN top_depts td ON e.department = td.department ORDER BY e.salary DESC; -- Recursive CTE — traverse hierarchies (org charts, graphs) WITH RECURSIVE org AS ( SELECT id, name, manager_id, 0 AS depth FROM employees WHERE manager_id IS NULL -- root (CEO) UNION ALL SELECT e.id, e.name, e.manager_id, o.depth + 1 FROM employees e JOIN org o ON e.manager_id = o.id -- join to parent ) SELECT * FROM org ORDER BY depth, name;

Recursive CTEs are the standard way to traverse hierarchical data — org charts, category trees, bill of materials. The anchor member (base case) runs once; the recursive member repeats until no new rows are added.

Data Modification

INSERT, UPDATE, DELETE, and UPSERT — writing data safely

Why

Writes are irreversible unless you wrap them in a transaction. An UPDATE without a WHERE clause updates every row in the table — a mistake that has ended careers. Understanding DML (Data Manipulation Language) and how transactions protect you from these mistakes is essential before touching production data.

Intuition

DML statements (INSERT, UPDATE, DELETE) write data; DDL statements (CREATE, ALTER, DROP) change the schema. Both should be wrapped in a transaction when making changes that need to be atomic — either all succeed or all roll back. Always preview writes with a SELECT first using the same WHERE clause you plan to use.

Explanation

INSERT, UPDATE, DELETE

-- INSERT INSERT INTO users (name, email, created_at) VALUES ('Alice', 'alice@example.com', NOW()); -- INSERT from SELECT INSERT INTO archive_orders SELECT * FROM orders WHERE created_at < '2023-01-01'; -- UPDATE — ALWAYS include WHERE unless you mean to update everything BEGIN; UPDATE users SET status = 'inactive' WHERE last_login < NOW() - INTERVAL '1 year'; -- preview rows affected: rowcount shown COMMIT; -- or ROLLBACK; if something looks wrong -- DELETE — same discipline: WHERE required DELETE FROM sessions WHERE expires_at < NOW(); -- TRUNCATE — removes all rows instantly, no WHERE, no rollback in most DBs TRUNCATE TABLE temp_staging;

The pattern of wrapping UPDATE/DELETE in BEGIN ... COMMIT and keeping a ROLLBACK ready is standard practice for any manual data change in production. Run the equivalent SELECT first to confirm row count.

UPSERT — INSERT or UPDATE

-- Postgres: ON CONFLICT INSERT INTO user_stats (user_id, login_count, last_login) VALUES (42, 1, NOW()) ON CONFLICT (user_id) DO UPDATE SET login_count = user_stats.login_count + 1, last_login = EXCLUDED.last_login; -- EXCLUDED refers to the row that was attempted to be inserted -- MySQL / SQLite: ON DUPLICATE KEY UPDATE / REPLACE INTO INSERT INTO user_stats (user_id, login_count) VALUES (42, 1) ON DUPLICATE KEY UPDATE login_count = login_count + 1; -- SQL Server: MERGE MERGE INTO targets AS t USING sources AS s ON t.id = s.id WHEN MATCHED THEN UPDATE SET t.value = s.value WHEN NOT MATCHED THEN INSERT (id, value) VALUES (s.id, s.value);

UPSERT is idempotent — safe to run multiple times, which makes it ideal for data pipelines where records may be re-ingested.

Schema & Indexes

Table design, keys, constraints, and how indexes make or break query speed

Why

A well-designed schema prevents entire classes of bugs — referential integrity violations, duplicate records, invalid states. Indexes are the primary lever for query performance: a missing index on a join column can turn a 10ms query into a 10-second full table scan. Understanding when and why indexes help (and when they hurt) is essential for both schema design and query tuning.

Intuition

A B-tree index is like a book's index — it sorts values so the database can jump to a row in O(log n) rather than scanning the whole table O(n). The cost: every write (INSERT, UPDATE, DELETE) must also update the index. Indexes make reads faster and writes slightly slower — the trade-off is almost always worth it for columns used frequently in WHERE, JOIN, and ORDER BY.

Normalization reduces redundancy by splitting data into separate tables linked by foreign keys. The trade-off: fewer writes to update in one place, but more joins to read data back together.

Explanation

Keys and constraints

CREATE TABLE orders ( id SERIAL PRIMARY KEY, -- unique, not null, clustered customer_id INT NOT NULL REFERENCES customers(id) -- foreign key — referential integrity ON DELETE CASCADE, -- delete orders if customer deleted status TEXT NOT NULL CHECK (status IN ('pending','shipped','delivered','cancelled')), amount NUMERIC(10, 2) NOT NULL CHECK (amount > 0), created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), UNIQUE (customer_id, created_at) -- composite unique constraint ); -- Constraints the DB enforces automatically: -- PRIMARY KEY: unique + not null -- FOREIGN KEY: referenced row must exist -- UNIQUE: no duplicates in the column(s) -- CHECK: boolean expression must be true -- NOT NULL: value required

Indexes — types and when to use them

-- Single-column index — speeds up equality and range filters on that column CREATE INDEX idx_orders_customer ON orders(customer_id); -- Composite index — order matters: most selective / most-queried column first CREATE INDEX idx_orders_status_date ON orders(status, created_at); -- Useful for: WHERE status = 'pending' ORDER BY created_at -- NOT useful for: WHERE created_at > '2024-01-01' (leading col not used) -- Covering index — includes extra columns to avoid reading the base table CREATE INDEX idx_orders_covering ON orders(customer_id) INCLUDE (amount, status); -- Query can be answered entirely from the index — no table lookup -- Partial index — index only a subset of rows CREATE INDEX idx_active_orders ON orders(created_at) WHERE status = 'pending'; -- much smaller, faster for pending-only queries -- When NOT to add an index: -- Small tables (full scan is fine), low-cardinality columns (boolean, status), -- heavy-write tables where index maintenance cost outweighs read benefit

Query Performance

Reading execution plans, finding bottlenecks, and making slow queries fast

Why

Writing correct SQL is the baseline. Writing SQL that stays fast at scale is what separates strong engineers. A query that works in dev on 10k rows can become a minute-long scan in production on 100M rows. EXPLAIN tells you exactly what the database is doing — which indexes it's using, how many rows it's scanning, where the cost is. Without it, optimisation is guesswork.

Intuition

The query optimizer reads your SQL, generates several possible execution plans, estimates the cost of each, and picks the cheapest one based on table statistics. EXPLAIN shows you that chosen plan. EXPLAIN ANALYZE actually runs the query and shows real timing — the gap between estimated and actual rows is where optimizer mistakes live.

The two things to look for: Seq Scan on a large table (bad — full scan, missing index) and Nested Loop on large outer sets (can be bad — O(n) index lookups). Index Scan and Hash Join are usually what you want.

Explanation

EXPLAIN and EXPLAIN ANALYZE

-- EXPLAIN — show the execution plan without running the query EXPLAIN SELECT * FROM orders WHERE customer_id = 42; -- EXPLAIN ANALYZE — run the query and show actual vs estimated times EXPLAIN ANALYZE SELECT o.id, c.name, o.amount FROM orders o JOIN customers c ON o.customer_id = c.id WHERE o.status = 'pending' ORDER BY o.created_at; -- Key things to look for in the output: -- Seq Scan → full table scan — likely missing index -- Index Scan → using an index — good -- Index Only Scan → serving from index alone (covering index) — best -- Hash Join → efficient for large tables -- Nested Loop → good for small sets, can be slow for large ones -- rows= estimate vs actual → large gap means stale statistics -- run ANALYZE table_name to refresh

Common performance patterns

-- 1. Avoid functions on indexed columns in WHERE — prevents index use -- BAD: WHERE YEAR(created_at) = 2024 -- GOOD: WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01' -- 2. Avoid SELECT * in production queries -- Fetches all columns including large text/json blobs unnecessarily -- 3. Use EXISTS instead of COUNT for existence checks -- BAD (scans and counts all matching rows): WHERE (SELECT COUNT(*) FROM orders WHERE customer_id = c.id) > 0 -- GOOD (stops at the first match): WHERE EXISTS (SELECT 1 FROM orders WHERE customer_id = c.id) -- 4. Pagination — OFFSET gets slower as page number grows -- BAD for large offsets: SELECT * FROM events ORDER BY id LIMIT 20 OFFSET 100000 -- GOOD — keyset pagination (use last seen id): SELECT * FROM events WHERE id > :last_id ORDER BY id LIMIT 20

N+1 and join vs subquery performance

-- N+1 pattern: running a query per row of an outer result -- This is almost always wrong in SQL — use a JOIN instead -- BAD (conceptually — N+1 in an ORM context): -- For each customer, SELECT their latest order separately -- GOOD: one query with a window function SELECT DISTINCT ON (customer_id) customer_id, id AS order_id, amount, created_at FROM orders ORDER BY customer_id, created_at DESC; -- Postgres DISTINCT ON -- Or with ROW_NUMBER: SELECT customer_id, order_id, amount FROM ( SELECT customer_id, id AS order_id, amount, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn FROM orders ) t WHERE rn = 1;