You already know how to pull data, filter it, join tables, and group rows. This guide takes you one step further — and the goal here is for each idea to click, not just to show you syntax. We'll go slowly and use plain language.
#Window Functions
Here's the problem window functions solve.
When you use GROUP BY, you squash many rows into one. If you group sales by region, you get one row per region — the individual sales disappear. That's great for a summary, but sometimes you want to keep every row AND add a summary number next to it. For example: "show me every sale, and next to each one, tell me how it ranks within its region."
GROUP BY can't do that, because it throws the rows away. A window function can. It looks at a "window" of related rows, does a calculation, and writes the answer onto each row without removing anything.
The key word is OVER. Whenever you see OVER, think: "calculate this over a window of rows, but keep all the rows."
1SELECT
2 region,
3 salesperson,
4 amount,
5 RANK() OVER (PARTITION BY region ORDER BY amount DESC) AS rank_in_region
6FROM sales;
Let's read the OVER (...) part like a sentence:
PARTITION BY regionmeans "split the rows into separate buckets, one per region." It's likeGROUP BY, but the rows don't get squashed — they just get sorted into piles.ORDER BY amount DESCmeans "within each pile, sort from biggest sale to smallest."RANK()then numbers them: 1 for the biggest, 2 for the next, and so on — starting over for each region.
So you get every sale listed, each tagged with its rank inside its own region. Nothing disappeared.
#Three ways to number rows
These look similar but handle ties differently:
ROW_NUMBER()— just counts: 1, 2, 3, 4. Even if two values are equal, they get different numbers.RANK()— ties get the same number, then it skips: 1, 1, 3.DENSE_RANK()— ties get the same number, but it doesn't skip: 1, 1, 2.
Think of a race: two runners tie for first. RANK says they're both "1st" and the next person is "3rd." DENSE_RANK says the next person is "2nd."
#Running totals
A "running total" is a sum that grows as you go down the list — like a bank balance after each transaction. Window functions do this naturally because they can look at "all rows up to this one":
1SELECT
2 sale_date,
3 amount,
4 SUM(amount) OVER (ORDER BY sale_date) AS running_total
5FROM sales;
Read it as: "for each row, add up the amounts from the start of the list down to here." The first row shows its own amount; the second shows the first two added together; and so on.
#Peeking at the row before or after: LAG and LEAD
Often you want to compare a row to the one next to it — like "how much did revenue change from last month?" Normally that's awkward, because SQL looks at one row at a time and rows don't easily "see" their neighbors.
LAG solves this: it grabs a value from a previous row. LEAD grabs from a later row.
1SELECT
2 month,
3 revenue,
4 LAG(revenue) OVER (ORDER BY month) AS last_month_revenue,
5 revenue - LAG(revenue) OVER (ORDER BY month) AS change
6FROM monthly_revenue;
LAG(revenue) says "give me the revenue from the row above this one." Now each row knows both its own revenue and last month's, so subtracting them gives you the change. No complicated self-join needed.
#Common Table Expressions (CTEs)
A CTE is just a way to give a name to a query so you can use it like a temporary table. You write it with the word WITH.
Why bother? Because nested subqueries get hard to read fast. A CTE lets you build your query in clear, named steps — top to bottom, like a recipe.
1WITH regional_avg AS (
2 SELECT region, AVG(amount) AS avg_amount
3 FROM sales
4 GROUP BY region
5)
6SELECT s.salesperson, s.region, s.amount
7FROM sales s
8JOIN regional_avg s_avg ON s.region = s_avg.region
9WHERE s.amount > s_avg.avg_amount;
Step one (the WITH block): figure out the average sale for each region, and call that table regional_avg. Step two: join it back to the sales and keep only sales that beat their region's average. Two clear steps instead of one tangled query.
#Recursive CTEs — for tree-shaped data
Some data is shaped like a tree: an employee reports to a manager, who reports to their manager, and so on. Or product categories with subcategories inside subcategories. You don't know how many levels deep it goes, so a normal query can't follow the chain.
A recursive CTE can. The idea is simple once you see it: a query that refers to itself to keep walking down the chain, one level at a time, until there's nothing left to find.
It always has two parts joined by UNION ALL:
- A starting point (the anchor) — where to begin.
- A repeating step — "given what I've found so far, find the next level."
1WITH RECURSIVE org_chart AS (
2 -- Start: the top boss, who has no manager
3 SELECT employee_id, name, manager_id, 1 AS level
4 FROM employees
5 WHERE manager_id IS NULL
6
7 UNION ALL
8
9 -- Repeat: find everyone who reports to someone we already found
10 SELECT e.employee_id, e.name, e.manager_id, oc.level + 1
11 FROM employees e
12 JOIN org_chart oc ON e.manager_id = oc.employee_id
13)
14SELECT * FROM org_chart ORDER BY level;
It starts with the top boss (level 1). Then it finds everyone reporting to the boss (level 2). Then everyone reporting to them (level 3). It keeps repeating until nobody new turns up, then stops. The level column tracks how deep each person sits.
#Turning Rows into Columns (Pivoting)
Imagine your sales data has a row per quarter, but you want one row per product with four columns — Q1, Q2, Q3, Q4 — side by side. That's "pivoting": flipping rows into columns.
The trick is CASE inside SUM. Think of CASE as an if/then: "if this row is Q1, count its amount; otherwise count zero."
1SELECT
2 product,
3 SUM(CASE WHEN quarter = 'Q1' THEN amount ELSE 0 END) AS q1,
4 SUM(CASE WHEN quarter = 'Q2' THEN amount ELSE 0 END) AS q2,
5 SUM(CASE WHEN quarter = 'Q3' THEN amount ELSE 0 END) AS q3,
6 SUM(CASE WHEN quarter = 'Q4' THEN amount ELSE 0 END) AS q4
7FROM sales
8GROUP BY product;
Each SUM(CASE...) builds one column by only adding up the amounts that belong in it and ignoring the rest. The result is a tidy grid.
#Automatic Subtotals: ROLLUP
Normally GROUP BY region, product gives you a total for each region-and-product combination. But what if you also want a subtotal for each whole region, plus a grand total at the bottom — all in one query? That's what ROLLUP adds:
1SELECT region, product, SUM(amount)
2FROM sales
3GROUP BY ROLLUP (region, product);
You get the normal detailed rows, plus a subtotal row for each region, plus one grand-total row — without writing three separate queries and stitching them together.
#One Confusing Thing That Trips Everyone Up
SQL is written in one order but runs in a different order. You write SELECT first, but the database actually does FROM and WHERE first, and only gets to SELECT near the end.
The rough order the database actually follows:
FROM/JOIN— gather the tablesWHERE— filter individual rowsGROUP BY— form the groupsHAVING— filter the groupsSELECT— pick columns and run window functionsORDER BY— sortLIMIT— keep only the first few
This explains a frustrating error: you can't use a window function inside WHERE. Why? Because WHERE runs at step 2, but window functions don't happen until step 5 — at the moment WHERE runs, the window function's answer doesn't exist yet.
The fix: wrap your query in a CTE, then filter in an outer query that runs afterward.
1WITH ranked AS (
2 SELECT
3 salesperson,
4 amount,
5 RANK() OVER (ORDER BY amount DESC) AS rnk
6 FROM sales
7)
8SELECT * FROM ranked WHERE rnk <= 3; -- top 3
The inner query calculates the rank; the outer query, which runs later, filters on it. Problem solved.
#What to Learn Next
When these feel comfortable, good next steps are: how to make queries fast (indexes and the EXPLAIN command, which shows you what the database is actually doing), and how databases stay correct when many people use them at once (transactions). Those move you from "writing a query that works" to "writing a query that works well."