Advanced SQL
Advanced SQL is mostly about expressing multi-step logic in one readable query instead of exporting data to Python. The core tools are common table expressions (CTEs) to break a query into named steps, window functions to compute running totals, rankings and comparisons across rows without collapsing them, and MERGE to upsert data. These features are standard SQL and work in PostgreSQL, Snowflake, BigQuery, Spark SQL, Trino and DuckDB with small syntax differences.
Common table expressions (CTEs)
A CTE names a subquery so the main query reads top to bottom like a pipeline:
WITH paid AS (
SELECT * FROM orders WHERE status = 'paid'
),
by_customer AS (
SELECT customer_id, COUNT(*) AS orders, SUM(amount) AS revenue
FROM paid
GROUP BY customer_id
)
SELECT *
FROM by_customer
WHERE revenue > 1000;This is the style most dbt models are written in. Modern optimizers usually inline CTEs, so they cost nothing extra; check the plan if a CTE is referenced many times.
Recursive CTEs walk hierarchies such as org charts or category trees:
WITH RECURSIVE chain AS (
SELECT id, manager_id, name, 1 AS depth FROM employees WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.manager_id, e.name, c.depth + 1
FROM employees e JOIN chain c ON e.manager_id = c.id
)
SELECT * FROM chain ORDER BY depth;Window functions
A window function computes a value for each row using a window of related rows, and unlike GROUP BY it keeps every row. The window is defined by OVER (...):
PARTITION BYsplits rows into independent groups (per city, per customer).ORDER BYorders rows within each partition.- A frame (
ROWS BETWEEN ...) picks which rows around the current one to include.
- Daily revenue for two cities. We want a 3-day moving average that restarts for each city.
- Row 1 (New York, Nov 1): no preceding rows yet, so the frame holds only itself. avg = 100.
- Row 2: the frame is rows 1–2, because only one preceding row exists. avg = 120.
- Row 3: the full frame, 2 preceding + current. avg = (100 + 140 + 90) / 3 = 110.
- Row 4: the frame slides down one row and Nov 1 drops out. avg = (140 + 90 + 130) / 3 = 120.
- Toronto is a new partition: the frame resets and never reaches into New York's rows. avg = 60.
- All six input rows are still in the output. Window functions add a column; GROUP BY would have collapsed the rows.
The main window functions
| Family | Functions | Example use |
|---|---|---|
| Ranking | ROW_NUMBER, RANK, DENSE_RANK, NTILE |
Top 3 products per category, deduplicate rows |
| Offset | LAG, LEAD, FIRST_VALUE, LAST_VALUE |
Change vs previous day, time between orders |
| Aggregate | SUM, AVG, COUNT, MIN, MAX with OVER |
Running totals, moving averages, share of total |
SELECT customer_id, order_id, ordered_at, amount,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY ordered_at) AS order_seq,
ordered_at - LAG(ordered_at) OVER (PARTITION BY customer_id ORDER BY ordered_at) AS since_prev,
SUM(amount) OVER (PARTITION BY customer_id ORDER BY ordered_at) AS running_total,
amount / SUM(amount) OVER (PARTITION BY customer_id) AS share_of_customer
FROM orders;ROW_NUMBER gives 1, 2, 3, 4 even for ties; RANK gives 1, 2, 2, 4; DENSE_RANK gives 1, 2, 2, 3.
Filtering on a window: QUALIFY
You cannot use a window function in WHERE (it runs before SELECT). Either wrap the query in a CTE, or use QUALIFY, supported by Snowflake, BigQuery, Databricks, DuckDB and others (not PostgreSQL):
-- keep the latest version of each customer (a classic dedup)
SELECT *
FROM customers_raw
QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC) = 1;More aggregation: GROUPING SETS, ROLLUP, CUBE
Compute several levels of totals in one pass:
SELECT city, status, SUM(amount) AS revenue
FROM orders
GROUP BY ROLLUP (city, status); -- per (city, status), per city, and grand totalCUBE (city, status) adds every combination; GROUPING SETS ((city), (status), ()) lists exactly the levels you want. FILTER (WHERE ...) computes conditional aggregates: COUNT(*) FILTER (WHERE status = 'paid') (PostgreSQL, DuckDB, Trino, Spark SQL); engines without it use COUNT(CASE WHEN status = 'paid' THEN 1 END).
Upserts with MERGE
MERGE inserts, updates or deletes in one atomic statement, which is how incremental loads into warehouses and lakehouse tables (Delta, Iceberg) are written:
MERGE INTO dim_customer t
USING staged_customers s
ON t.customer_id = s.customer_id
WHEN MATCHED AND t.city <> s.city THEN
UPDATE SET city = s.city, updated_at = s.updated_at
WHEN NOT MATCHED THEN
INSERT (customer_id, city, updated_at) VALUES (s.customer_id, s.city, s.updated_at);PostgreSQL supports MERGE since version 15 and also has INSERT ... ON CONFLICT (customer_id) DO UPDATE. Make sure the source has one row per key, or the merge fails or behaves unpredictably.
Views and materialized views
- A view is a saved query. It always shows current data and costs a full query each time it is read.
- A materialized view stores the result. Reads are fast; the data is as fresh as the last refresh. PostgreSQL refreshes manually (
REFRESH MATERIALIZED VIEW sales_summary;, or... CONCURRENTLYto keep it readable during the refresh, which requires a unique index on the view); Snowflake, BigQuery and ClickHouse can maintain some materialized views incrementally and automatically.
CREATE MATERIALIZED VIEW sales_summary AS
SELECT city, date_trunc('day', ordered_at) AS day, SUM(amount) AS revenue
FROM orders
WHERE status = 'paid'
GROUP BY 1, 2;In dbt, the same choice is the model’s materialization: view, table, incremental or materialized_view.
Reading the query plan
EXPLAIN shows how the engine will run a query; EXPLAIN ANALYZE runs it and shows real timings and row counts. Look for full scans of big tables where you expected an index or partition pruning, row estimates far from the actual counts, and joins that explode row counts. See indexing.
Common mistakes
- Default frame surprise: with
ORDER BYand no frame, the default isRANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Ties are treated as one peer group, so a running total jumps by several rows at once, andLAST_VALUEreturns the current row (or its last tie), not the last row of the partition. Spell outROWS BETWEEN ...when it matters. - Using
ROW_NUMBERfor dedup without a deterministicORDER BY, so reruns keep different rows. ROWS BETWEEN 6 PRECEDINGas a “7-day average” when some days have no rows. Fill gaps with a calendar table or useRANGEwith an interval.MERGEfrom a source with duplicate keys.- Deeply nested subqueries instead of named CTEs that someone else can read.
Learning Resources
Docs
- PostgreSQL: Window Functions tutorial
- PostgreSQL: WITH Queries (CTEs)
- Modern SQL: standard features and support across databases
- Use The Index, Luke: performance and query plans
Tutorials
Books
- SQL Antipatterns, Volume 1: Bill Karwin
- SQL Performance Explained: Markus Winand