Every data tool eventually turns into SQL: the warehouse runs it, the transformation framework generates it, the orchestrator schedules it, and the dashboard hides it behind a chart. Engineers who can read and write SQL fluently debug pipelines in minutes that take others a day, because they can see what the system is actually doing to the data. This module goes past the basics you probably know into the parts that matter for data work: joins that do not silently multiply rows, aggregation with grouping sets, window functions, common table expressions, and reading an execution plan to understand why a query is slow. Examples use PostgreSQL syntax, which most warehouses follow closely.
- Write joins deliberately and detect fan-out when a join multiplies rows
- Use aggregation, HAVING, DISTINCT and conditional aggregates correctly
- Apply window functions for rankings, running totals, deduplication and gaps
- Structure complex queries with CTEs and know when a CTE hurts performance
- Read an EXPLAIN plan and fix the most common causes of slow queries
Joins, and the row-multiplication trap
A join combines rows from two tables where a condition holds. INNER keeps only matching pairs; LEFT keeps every row from the left table and fills the right side with NULLs when nothing matches; FULL keeps everything from both. The mistake that produces wrong numbers in more reports than any other is fan-out: joining a one-row-per-customer table to a many-rows-per-customer table, then summing a column from the one side. Each customer's amount is counted once per matching row on the many side.
-- WRONG: orders x addresses multiplies each order by the customer's address count
SELECT c.id, SUM(o.amount) AS revenue
FROM customers c
JOIN orders o ON o.customer_id = c.id
JOIN addresses a ON a.customer_id = c.id
GROUP BY c.id;
-- RIGHT: reduce the many side to one row per key before joining
WITH primary_address AS (
SELECT DISTINCT ON (customer_id) customer_id, city
FROM addresses
ORDER BY customer_id, is_primary DESC, created_at
)
SELECT c.id, pa.city, SUM(o.amount) AS revenue
FROM customers c
JOIN orders o ON o.customer_id = c.id
LEFT JOIN primary_address pa ON pa.customer_id = c.id
GROUP BY c.id, pa.city;Two habits prevent fan-out. Know the grain of every table you join (one row per what?), and check row counts before and after a join: if COUNT(*) grows when you add a table that should be one-to-one, you have found it. Also be careful with filters on the right side of a LEFT JOIN: putting a.status = 'active' in the WHERE clause turns the LEFT JOIN into an INNER JOIN because NULL rows fail the filter; put the condition in the ON clause to keep unmatched rows.
Anti-joins answer "which A have no B": LEFT JOIN b ... WHERE b.id IS NULL, or WHERE NOT EXISTS (SELECT 1 FROM b WHERE b.a_id = a.id). Prefer NOT EXISTS over NOT IN when the subquery can return NULLs; NOT IN with a NULL matches nothing.
Aggregation done precisely
GROUP BY collapses rows sharing the grouping columns; every column in the SELECT must be either grouped or aggregated. HAVING filters groups after aggregation, WHERE filters rows before it, and the difference is both correctness and performance. COUNT(*) counts rows, COUNT(col) counts non-NULL values, COUNT(DISTINCT col) counts distinct non-NULL values; mixing them up is a classic off-by-NULL error. Conditional aggregates (SUM(CASE WHEN ...) or PostgreSQL's FILTER) replace many self-joins.
SELECT
date_trunc('month', ordered_at) AS month,
COUNT(*) AS orders,
COUNT(DISTINCT customer_id) AS customers,
SUM(amount) FILTER (WHERE status = 'paid') AS paid_revenue,
SUM(amount) FILTER (WHERE status = 'refunded') AS refunds,
AVG(amount) AS avg_order,
ROUND(100.0 * COUNT(*) FILTER (WHERE status = 'refunded') / COUNT(*), 2) AS refund_pct
FROM orders
WHERE ordered_at >= date '2026-01-01'
GROUP BY 1
HAVING COUNT(*) >= 100
ORDER BY 1;Integer division bites here: COUNT(a) / COUNT(*) is integer division in most databases and returns 0; multiply by 100.0 or cast first. And AVG ignores NULLs, so an average over a column with missing values is an average of the present values, which may or may not be what the business meant. State the treatment of NULLs explicitly in the query and in the column's documentation.
GROUP BY ROLLUP (region, country) or GROUPING SETS produce subtotals and grand totals in one query; they are the honest way to build the totals row rather than a second query that may disagree.
Window functions: aggregate without collapsing
A window function computes a value over a set of related rows (the window) while keeping every row. OVER (PARTITION BY ... ORDER BY ...) defines the window: partition restarts the calculation per group, order defines sequence for ranking and running calculations. They replace correlated subqueries and self-joins for rankings, running totals, moving averages, previous-row comparisons and deduplication, and they are usually faster.
-- top 3 orders per customer
SELECT * FROM (
SELECT o.*, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC) AS rn
FROM orders o
) t WHERE rn <= 3;
-- keep the latest row per key (dedup after a re-load)
SELECT * FROM (
SELECT e.*, ROW_NUMBER() OVER (PARTITION BY event_id ORDER BY loaded_at DESC) AS rn
FROM events_raw e
) t WHERE rn = 1;
-- running revenue per customer, and the gap since their previous order
SELECT
customer_id, ordered_at, amount,
SUM(amount) OVER (PARTITION BY customer_id ORDER BY ordered_at
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total,
ordered_at - LAG(ordered_at) OVER (PARTITION BY customer_id ORDER BY ordered_at) AS since_previous
FROM orders;ROW_NUMBER gives a unique sequence, RANK leaves gaps after ties, DENSE_RANK does not. The frame clause (ROWS BETWEEN ...) matters for running calculations: the default frame with ORDER BY is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which treats tied order values as one group and can surprise you; say ROWS when you mean rows. Window functions run after WHERE and GROUP BY, so you cannot filter on them directly; wrap the query, as the examples do.
CTEs and query structure
A common table expression (WITH name AS (...)) names a subquery so a complex query reads top to bottom as a sequence of steps: clean, filter, aggregate, join, present. It is the unit of readability in analytics SQL and the unit that transformation frameworks like dbt turn into models. Two cautions. In PostgreSQL before version 12, CTEs were always materialised (an optimisation fence); modern versions inline them unless referenced more than once or marked MATERIALIZED. And a CTE referenced twice may be computed twice in some engines; check the plan.
WITH paid_orders AS (
SELECT customer_id, ordered_at::date AS order_date, amount
FROM orders
WHERE status = 'paid' AND ordered_at >= date '2026-01-01'
),
daily_customer AS (
SELECT customer_id, order_date, SUM(amount) AS day_amount
FROM paid_orders
GROUP BY customer_id, order_date
),
with_streaks AS (
SELECT *,
order_date - (ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date))::int AS streak_key
FROM daily_customer
)
SELECT customer_id, MIN(order_date) AS streak_start, COUNT(*) AS consecutive_days, SUM(day_amount) AS streak_amount
FROM with_streaks
GROUP BY customer_id, streak_key
HAVING COUNT(*) >= 3
ORDER BY consecutive_days DESC;The streak_key trick (date minus row number is constant within a run of consecutive days) is the standard gaps-and-islands technique; it is worth recognising because interview questions and real pipelines both use it. Recursive CTEs handle hierarchies (org charts, bill of materials) and sequences; they are the one place SQL loops.
Reading the plan
EXPLAIN shows how the planner intends to run a query; EXPLAIN (ANALYZE, BUFFERS) runs it and reports actual rows, time and I/O per step. Read plans from the innermost node outward and look for four things: sequential scans on large tables where an index would help, row estimate errors (estimated 100, actual 2 million, which means stale statistics and a wrong join strategy), nested loop joins over large inputs (usually a missing index on the inner side), and sorts or hashes spilling to disk (work memory too small for the operation).
EXPLAIN (ANALYZE, BUFFERS)
SELECT c.email, o.amount
FROM orders o JOIN customers c ON c.id = o.customer_id
WHERE o.ordered_at >= now() - interval '1 day';
-- Nested Loop (cost=0.43..8.47 rows=1 width=40) (actual time=0.05..2411.9 rows=18230 loops=1)
-- -> Seq Scan on orders o (cost=0..145213 rows=1 width=16) (actual time=0.02..2380.1 rows=18230 loops=1)
-- Filter: (ordered_at >= (now() - '1 day'::interval))
-- Rows Removed by Filter: 9981770
-- -> Index Scan using customers_pkey on customers c (actual time=0.001..0.001 rows=1 loops=18230)
-- Planning Time: 0.3 ms Execution Time: 2418.7 ms
-- fixes: an index on orders(ordered_at), and fresh statistics
CREATE INDEX CONCURRENTLY orders_ordered_at_idx ON orders (ordered_at);
ANALYZE orders;In columnar warehouses the vocabulary changes (partitions pruned, bytes scanned, shuffle) but the questions are the same: how much data is read, is the filter applied early, does a join move a large table across the network. The warehouse module goes deeper; the habit to build now is to look at the plan before guessing.
Wrapping an indexed column in a function (WHERE date(ordered_at) = ..., WHERE lower(email) = ...) prevents the index from being used. Rewrite as a range (ordered_at >= d AND ordered_at < d + 1) or create an expression index.