Data7 min read

Advanced SQL

Master SQL beyond basic CRUD - window functions, CTEs, recursive queries, query optimization, and execution plans for complex data analysis.

SQLPostgreSQLWindow FunctionsCTEsQuery Optimization

skill: Advanced SQL category: Data tags: [SQL, PostgreSQL, Window Functions, CTEs, Query Optimization] readingTime: 7

SQL is the most important data skill and the most underutilized. Most developers know SELECT, WHERE, and JOIN - but window functions, CTEs, and query optimization separate good from excellent. Learn PostgreSQL as your standard; its SQL dialect is both most standard-compliant and most feature-rich.

Workflow

Use CTEs for readable, modular queriesCommon Table Expressions make complex queries readable by breaking them into named steps - like functions for SQL. They also enable recursive queries.
Master window functionsWindow functions compute aggregates across a set of rows related to the current row - without collapsing the result set like GROUP BY does.
Write recursive CTEsRecursive CTEs traverse hierarchical data - org charts, category trees, bill of materials - without multiple round trips or application-side recursion.
Analyze query performance with EXPLAIN ANALYZESlow queries are usually caused by missing indexes, wrong join order, or full table scans. EXPLAIN ANALYZE shows the actual execution plan and where time is spent.
Optimize with proper indexing strategyThe right index turns a 10-second query into a 10ms query. The wrong index (on every column) slows writes and wastes disk space.

Output Format

SQL solutions using CTEs for readability, window functions for row-level analytics, and indexed queries with EXPLAIN ANALYZE.

WITH step_1 AS (
  -- first aggregation
),
step_2 AS (
  -- second aggregation referencing step_1
)
SELECT ...
FROM step_2
ORDER BY ...
LIMIT 100;

Examples

Input

Output

Edge Cases & Gotchas

NULL in aggregate functions - COUNT(*) counts all rows; COUNT(column) ignores NULLs. SUM(NULL) returns NULL, not 0. Use COALESCE(SUM(col), 0) to treat NULL as 0.
DISTINCT vs. GROUP BY - SELECT DISTINCT is usually less efficient than GROUP BY for deduplication at scale - GROUP BY can leverage sort-based aggregation; DISTINCT uses hash deduplication.
Large IN() lists - WHERE id IN (1000 values) creates a slow sequential scan or large hash table. Use a temporary table or CTE + JOIN instead.
Locking on updates - UPDATE without an index on the WHERE clause locks the table longer. Always ensure the WHERE clause in UPDATE/DELETE uses an indexed column.

When to Break These Rules

Use columnar databases (BigQuery, Redshift, DuckDB) instead of PostgreSQL for analytical queries over billions of rows - row-based storage is inefficient at that scale.
Use a DataFrame library (pandas, Polars) for exploratory analysis where schema is fluid and rapid iteration matters more than query correctness.

Reference Files

https://www.postgresql.org/docs/current/sql.html
https://use-the-index-luke.com/