Advanced SQL — Cheat Sheet

Tags: sql, postgresql, indexing, window-functions, cte, query-optimization, transactions

Level: advanced

TL;DR: Advanced SQL techniques for database performance, analytical window functions, query execution planning, and concurrency management in transactional systems.

Key concepts

Window functions perform analytical calculations across row sets without collapsing rows like GROUP BY.

ROW_NUMBER(), RANK(), and DENSE_RANK() handle tie ranking and row numbering differently within partitions.

Common Table Expressions (WITH queries) enhance readability and enable tree traversal using recursive queries.

B-Tree indexes optimize equality and range lookups but require leftmost-prefix adherence on composite keys.

Index covering avoids table access entirely by serving queries strictly out of the index pages.

Query planner calculates access paths (Index Scan vs. Bitmap Scan vs. Sequential Scan) using column statistics.

Correlated subqueries execute once per outer row, whereas joins and window functions allow vectorized processing.

Transaction isolation levels control concurrency trade-offs: Read Committed, Repeatable Read, and Serializable.

Partitioning breaks tables into smaller physical chunks (by range, list, or hash) to speed pruning.

Partial and expression-based indexes index subsets or computed columns to minimize footprint and query cost.

Typical questions and ready answers

Q: How do you determine whether a query is using an index effectively?R: I inspect EXPLAIN ANALYZE to check whether the planner chose an Index Scan or Sequential Scan, comparing estimated versus actual rows, and verifying filter conditions are not discarding rows after table retrieval.

Q: What is the operational difference between WHERE and HAVING?R: I use WHERE to filter individual rows before aggregation occurs, whereas HAVING applies filters over aggregate values after GROUP BY execution.

Q: How do recursive CTEs work and when should you use them?R: I use recursive CTEs for hierarchical and graph data; they consist of an anchor member unioned with a recursive member that references the CTE until returning an empty set.

Q: What causes deadlocks and how do you mitigate them in high-throughput workloads?R: Deadlocks occur when concurrent transactions acquire locks on multiple records in differing orders. I mitigate them by ensuring consistent lock acquisition ordering, keeping transactions short, and using optimistic locking where appropriate.

Pitfalls and common mistakes

Wrapping indexed columns in functions (e.g., WHERE DATE(created_at) = ...) prevents the engine from using standard B-Tree indexes.

Using SELECT * in production queries prevents index-only scans and increases memory usage, network overhead, and cache misses.

NOT IN with subqueries returning a single NULL evaluates the entire predicate to UNKNOWN and returns zero records; NOT EXISTS is safer.

Over-indexing tables slows down INSERT, UPDATE, and DELETE operations due to synchronous index maintenance costs.

Misunderstanding COUNT(column) vs COUNT(*); COUNT(column) ignores NULL entries whereas COUNT(*) counts total rows.

Snippets and commandsSQL

```-- Window function: 7-day rolling revenue and user ranking

SELECT

user_id,

order_date,

amount,

SUM(amount) OVER (

PARTITION BY user_id

ORDER BY order_date

ROWS BETWEEN 6 PRECEDING AND CURRENT ROW

) AS rolling_7d_spend,

DENSE_RANK() OVER (

PARTITION BY user_id

ORDER BY amount DESC

) AS rank_by_amount

FROM orders;

-- Recursive CTE: Category hierarchy traversal

WITH RECURSIVE category_tree AS (

SELECT id, parent_id, name, 1 AS depth

FROM categories

WHERE parent_id IS NULL

UNION ALL

SELECT c.id, c.parent_id, c.name, ct.depth + 1

FROM categories c

INNER JOIN category_tree ct ON c.parent_id = ct.id

)

SELECT * FROM category_tree ORDER BY depth, id;

-- Execution plan inspection and partial index creation

EXPLAIN (ANALYZE, BUFFERS)

SELECT id, email FROM users WHERE status = 'pending' AND created_at > NOW() - INTERVAL '30 days';

CREATE INDEX idx_users_pending_recent

ON users (created_at DESC)

WHERE status = 'pending';

```

To review

[ ] Frame specifications in window functions (ROWS vs RANGE vs GROUPS)

[ ] Lateral joins (CROSS JOIN LATERAL) for correlated subqueries

[ ] GIN and GiST indexes for JSONB and full-text search

[ ] Read phenomena differences: Dirty Read, Non-repeatable Read, and Phantom Read

[x] Basic join types (INNER, LEFT, RIGHT, FULL OUTER)

[x] Standard aggregation and GROUP BY rollups