All lessons
SQL cheat sheet
The SQL of the course on one page, each item with a short example, what it does in one line and the lesson that explains it. Examples use SQLite and the Northbay tables (mini_orders where you see amount). Print it or keep it open while you work.
Try any of these in the SQL sandbox.
How a query runs
Order you write it Lesson 1.4
SELECT … FROM … JOIN … WHERE … GROUP BY … HAVING … ORDER BY … LIMIT … The clauses always go in this order.
Order it runs Lesson 1.4
1 FROM and JOIN pick the tables, match rows
2 WHERE keep rows
3 GROUP BY form groups
4 HAVING keep groups
5 SELECT work out columns (window functions here)
6 DISTINCT drop repeated rows
7 ORDER BY sort
8 LIMIT / OFFSET keep the first few So WHERE cannot use a SELECT alias or an aggregate, but ORDER BY can use both.
SELECT and WHERE
Choose columns Lesson 2.1
SELECT product_name, unit_price * 1.13 AS with_tax
FROM products Columns, expressions and literals; AS gives a name.
Unique rows Lesson 2.1
SELECT DISTINCT city FROM customers Each combination of the chosen columns once.
Comparison Lesson 2.2
WHERE unit_price >= 50 -- = <> (or !=) < <= > >=
WHERE status = 'completed' -- text in single quotes Keeps rows where the condition is true.
AND, OR, NOT Lesson 2.3
WHERE channel = 'online' AND (city = 'Toronto' OR city = 'Ottawa') AND runs before OR, so always bracket mixed conditions.
IN, BETWEEN Lesson 2.3
WHERE store_id IN (1, 2, 5)
WHERE order_date BETWEEN '2025-01-01' AND '2025-03-31' A list of values; a range with both ends included.
LIKE Lesson 2.3
WHERE product_name LIKE '%pack%' -- % any run of characters
WHERE code LIKE 'A_1' -- _ exactly one Text patterns. SQLite ignores case for A–Z; PostgreSQL needs ILIKE.
Sort and take the top Lesson 2.5
ORDER BY unit_price DESC, product_id -- tie-breaker last
LIMIT 10 OFFSET 20 ASC is the default. Without ORDER BY, row order is not guaranteed.
CASE Lesson 2.6
CASE WHEN amount >= 100 THEN 'large'
WHEN amount >= 40 THEN 'medium'
ELSE 'small' END AS size_band If-then-else per row; the first true WHEN wins. No ELSE means NULL.
NULL rules
Test for blanks Lesson 2.4
WHERE email IS NULL
WHERE email IS NOT NULL Never write = NULL: it is never true.
Three-valued logic Lesson 2.4
NULL = NULL -- unknown, not true
5 + NULL -- NULL
'a' || NULL -- NULL
NOT (unknown) -- unknown WHERE keeps only true rows, so unknown rows quietly vanish.
Fill or guard Lesson 2.4
COALESCE(loyalty_tier, 'None') -- first non-NULL value
total / NULLIF(orders, 0) -- NULL instead of divide-by-zero COALESCE fills blanks; NULLIF turns a value into NULL.
Aggregates skip NULL Lesson 3.1
COUNT(*) -- every row
COUNT(email) -- rows where email is not NULL
AVG(x) -- average of non-NULL x only Only COUNT(*) counts NULLs.
The NOT IN trap Lesson 4.4
SELECT * FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM orders)
-- returns nothing: guest orders have a NULL customer_id. Use NOT EXISTS One NULL in the list makes every NOT IN test unknown.
Aggregates, GROUP BY and HAVING
Aggregates Lesson 3.1
SELECT COUNT(*), COUNT(DISTINCT customer_id),
SUM(amount), AVG(amount), MIN(amount), MAX(amount)
FROM mini_orders Many rows in, one row out.
One row per group Lesson 3.2
SELECT store_id, channel, COUNT(*) AS orders
FROM orders
GROUP BY store_id, channel Every non-aggregated column in SELECT goes in GROUP BY.
Filter groups Lesson 3.3
SELECT customer_id, COUNT(*) AS orders
FROM orders
WHERE status = 'completed' -- rows, before grouping
GROUP BY customer_id
HAVING COUNT(*) >= 5 -- groups, after grouping WHERE filters rows; HAVING filters groups using aggregates.
Count the right thing Lesson 3.4
COUNT(DISTINCT customer_id) -- people, not orders Distinct counts do not add up across groups; recalculate the total.
Conditional count Lesson 2.6
SUM(CASE WHEN channel = 'online' THEN 1 ELSE 0 END) AS online_orders Count or add only the rows you want, inside one GROUP BY.
Check the grain Lesson 3.5
SELECT order_id, COUNT(*) FROM order_items
GROUP BY order_id HAVING COUNT(*) > 1 Know what one row means before you add anything up.
Joins
INNER JOIN Lesson 4.2
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id Only rows that match on both sides.
LEFT JOIN Lesson 4.3
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id Every left row; NULLs where the right has no match.
RIGHT JOIN Lesson 4.3
FROM orders o RIGHT JOIN customers c ON … Every right row. Usually written as a LEFT JOIN with the tables swapped.
FULL JOIN Lesson 4.3
FROM a FULL JOIN b ON a.id = b.id Every row from both sides, matched where possible.
CROSS JOIN Lesson 5.4
FROM stores CROSS JOIN months Every row with every row. Handy with a one-row totals CTE.
Anti-join Lesson 4.4
SELECT c.* FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id)
-- or: LEFT JOIN orders o ON … WHERE o.order_id IS NULL Rows with no match: customers who never ordered.
Semi-join Lesson 4.4
WHERE EXISTS (SELECT 1 FROM returns r WHERE r.order_id = o.order_id) Rows with at least one match, without copying them.
Self-join Lesson 4.6
FROM employees e
LEFT JOIN employees m ON m.employee_id = e.manager_id A table joined to itself under two aliases.
USING Lesson 4.2
FROM orders JOIN order_items USING (order_id) Short form when both sides name the key the same.
Fan-out Lesson 4.5
-- orders 1→many order_items: an order-level number summed after the join counts once per line
-- fix: aggregate order_items first, then join A one-to-many join copies rows and inflates totals.
Stack results Lesson 4.7
SELECT … UNION ALL SELECT … -- keep every row
SELECT … UNION SELECT … -- remove duplicates
SELECT … INTERSECT SELECT … -- in both
SELECT … EXCEPT SELECT … -- in the first, not the second Same number of columns, same order, matching types.
Subqueries and CTEs
Scalar subquery Lesson 5.1
WHERE amount > (SELECT AVG(amount) FROM mini_orders) Returns one value, used like a number.
Derived table Lesson 5.1
SELECT AVG(n) FROM (
SELECT customer_id, COUNT(*) AS n FROM orders GROUP BY customer_id
) AS per_customer A subquery in FROM; give it an alias.
IN and EXISTS Lesson 5.2
WHERE order_id IN (SELECT order_id FROM returns)
WHERE EXISTS (SELECT 1 FROM returns r WHERE r.order_id = o.order_id) Membership tests against another query.
Correlated subquery Lesson 5.3
SELECT o.*, (SELECT MAX(o2.amount) FROM mini_orders o2
WHERE o2.customer_id = o.customer_id) AS their_max
FROM mini_orders o Uses the outer row, so it runs once per row.
CTE Lesson 5.4
WITH monthly AS (
SELECT strftime('%Y-%m', order_date) AS month, COUNT(*) AS orders
FROM orders GROUP BY month
),
best AS (SELECT MAX(orders) AS top FROM monthly)
SELECT m.* FROM monthly m CROSS JOIN best b WHERE m.orders = b.top Named steps at the top, read top to bottom.
Recursive CTE Lesson 5.5
WITH RECURSIVE cal(d) AS (
SELECT '2025-01-01' -- anchor
UNION ALL
SELECT date(d, '+1 day') FROM cal -- step
WHERE d < '2025-12-31' -- stop
)
SELECT d FROM cal Anchor, then a step repeated until it adds nothing. Calendars and org charts.
Window functions
The shape Lesson 6.1
function(…) OVER (
PARTITION BY group_cols -- optional: restart per group
ORDER BY sort_cols -- optional: order inside the group
ROWS BETWEEN … AND … -- optional: the frame
) Works over related rows but keeps every row.
Group total on every row Lesson 6.1
SUM(amount) OVER (PARTITION BY customer_id) AS customer_total Like GROUP BY without collapsing the rows.
Ranking Lesson 6.2
ROW_NUMBER() OVER (ORDER BY sales DESC, id) -- 1 2 3 4
RANK() OVER (ORDER BY sales DESC) -- 1 1 3 4
DENSE_RANK() OVER (ORDER BY sales DESC) -- 1 1 2 3 ROW_NUMBER never ties; add a tie-breaker so it is repeatable.
LAG and LEAD Lesson 6.4
LAG(revenue) OVER (ORDER BY month) -- previous row
LAG(revenue, 12) OVER (ORDER BY month) -- 12 rows back
LEAD(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) Compare with the row before or after.
Frames Lesson 6.5
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW -- running total
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW -- last 7 rows
ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING -- centred 7
RANGE … -- ties count together With ORDER BY and no frame, the default is RANGE up to the current row.
Moving average Lesson 6.6
AVG(sales) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) Smooths daily noise. Fill missing days first, or 7 rows is not 7 days.
Share, buckets, percentiles Lesson 6.7
1.0 * amount / SUM(amount) OVER () -- share of total
NTILE(4) OVER (ORDER BY spend) -- quartile 1–4
PERCENT_RANK() OVER (ORDER BY spend) -- 0 to 1
CUME_DIST() OVER (ORDER BY spend) -- share at or below OVER () with nothing inside is the whole result.
FIRST_VALUE Lesson 7.4
FIRST_VALUE(email) OVER (PARTITION BY customer_id ORDER BY joined_on) The value from the first row of the window.
Filtering a window Lesson 6.3
-- not allowed in WHERE: wrap it
WITH r AS (SELECT *, ROW_NUMBER() OVER (…) AS rn FROM t)
SELECT * FROM r WHERE rn = 1
-- BigQuery and Snowflake: QUALIFY ROW_NUMBER() OVER (…) = 1 Windows run after WHERE, so filter them in an outer query.
Types and cleaning
CAST Lesson 7.3
CAST('42' AS INTEGER) CAST(amount AS REAL) CAST(12.7 AS INTEGER) -- 12 Change type. CAST to INTEGER drops the fraction.
Whole-number division Lesson 7.3
7 / 2 -- 3
7 / 2.0 -- 3.5
100.0 * a / b -- a percentage that keeps decimals Two integers give an integer in SQLite, SQL Server and PostgreSQL.
Money and rounding Lesson 7.3
ROUND(0.1 + 0.2, 2) = 0.3 -- compare after rounding Decimals are stored inexactly; round before comparing.
Find duplicates Lesson 7.4
SELECT LOWER(TRIM(email)) AS k, COUNT(*)
FROM customers GROUP BY k HAVING COUNT(*) > 1 Group on a cleaned key and look for counts above 1.
Quality checks Lesson 7.5
-- orphans: should return no rows
SELECT o.* FROM orders o
LEFT JOIN customers c ON c.customer_id = o.customer_id
WHERE o.customer_id IS NOT NULL AND c.customer_id IS NULL A healthy check returns nothing.
Dates and text in SQLite, SQL Server and PostgreSQL
Dates are stored as ISO text ('2025-03-07') in SQLite, so they sort and compare correctly. Functions change between versions: check the official documentation for yours.
| Task | SQLite | SQL Server | PostgreSQL | Lesson |
|---|---|---|---|---|
| Today | date('now') | CAST(GETDATE() AS date) | CURRENT_DATE | 9.3 |
| Year, month | strftime('%Y', d), strftime('%m', d) | YEAR(d), MONTH(d) | EXTRACT(YEAR FROM d) | 7.1 |
| Month label | strftime('%Y-%m', d) | FORMAT(d, 'yyyy-MM') | to_char(d, 'YYYY-MM') | 7.1 |
| Weekday | strftime('%w', d) -- 0 = Sunday | DATEPART(weekday, d) | EXTRACT(DOW FROM d) -- 0 = Sunday | 7.1 |
| Start of month | date(d, 'start of month') | DATETRUNC(month, d) | date_trunc('month', d) | 7.1 |
| Add days | date(d, '+7 days') | DATEADD(day, 7, d) | d + INTERVAL '7 days' | 7.1 |
| End of month | date(d, 'start of month', '+1 month', '-1 day') | EOMONTH(d) | (date_trunc('month', d) + INTERVAL '1 month - 1 day')::date | 7.1 |
| Days between | julianday(b) - julianday(a) | DATEDIFF(day, a, b) | b - a | 7.1 |
| Join text | a || ' ' || b | a + ' ' + b or CONCAT(a, ' ', b) | a || ' ' || b or CONCAT(…) | 7.2 |
| Trim spaces | TRIM(x) | TRIM(x) (2017+) or LTRIM(RTRIM(x)) | TRIM(x) | 7.2 |
| Part of text | SUBSTR(x, 1, 3) | SUBSTRING(x, 1, 3) or LEFT(x, 3) | SUBSTR(x, 1, 3) or LEFT(x, 3) | 7.2 |
| Find text | INSTR(email, '@') | CHARINDEX('@', email) | STRPOS(email, '@') | 7.2 |
| Length | LENGTH(x) | LEN(x) -- ignores trailing spaces | LENGTH(x) | 7.2 |
| Upper, lower, replace | UPPER(x) LOWER(x) REPLACE(x, 'a', 'b') | same | same | 7.2 |
| First 10 rows | LIMIT 10 | SELECT TOP (10) … | LIMIT 10 | 9.3 |
| Replace NULL | COALESCE(x, 0) or IFNULL(x, 0) | COALESCE(x, 0) or ISNULL(x, 0) | COALESCE(x, 0) | 9.3 |
| Safe conversion | CAST(x AS INTEGER) -- never errors | TRY_CAST(x AS int) | no TRY_CAST: check with a regex first | 7.3 |
Analyst patterns
Top N per group Lesson 6.3
WITH ranked AS (
SELECT store_id, name, salary,
ROW_NUMBER() OVER (PARTITION BY store_id ORDER BY salary DESC, employee_id) AS rn
FROM employees
)
SELECT * FROM ranked WHERE rn <= 2 Use RANK instead of ROW_NUMBER to keep ties.
Latest row per key Lesson 6.3
WITH r AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id
ORDER BY order_date DESC, order_id DESC) AS rn
FROM orders
)
SELECT * FROM r WHERE rn = 1 Each customer's most recent order.
Month-over-month and YoY growth Lesson 8.1
WITH monthly AS (
SELECT strftime('%Y-%m', o.order_date) AS month,
SUM(i.quantity * i.unit_price * (1 - i.discount)) AS revenue
FROM orders o JOIN order_items i ON i.order_id = o.order_id
WHERE o.status = 'completed' GROUP BY month
)
SELECT month, revenue,
100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
/ NULLIF(LAG(revenue) OVER (ORDER BY month), 0) AS mom_pct,
100.0 * (revenue - LAG(revenue, 12) OVER (ORDER BY month))
/ NULLIF(LAG(revenue, 12) OVER (ORDER BY month), 0) AS yoy_pct
FROM monthly LAG 12 assumes no missing months; join to a calendar to be sure.
Running total and year-to-date Lesson 6.5
SELECT day, sales,
SUM(sales) OVER (ORDER BY day ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running,
SUM(sales) OVER (PARTITION BY substr(day, 1, 4) ORDER BY day) AS ytd
FROM daily Spell out ROWS so ties on day are not added together.
Remove duplicates, keep the first Lesson 7.4
WITH r AS (
SELECT customer_id,
ROW_NUMBER() OVER (PARTITION BY LOWER(TRIM(email))
ORDER BY joined_on, customer_id) AS rn
FROM customers WHERE email IS NOT NULL
)
SELECT customer_id FROM r WHERE rn > 1 -- the copies to drop Number the copies within each key; keep rn = 1.
Pivot rows into columns Lesson 8.6
SELECT department,
SUM(CASE WHEN year = '2024' THEN revenue ELSE 0 END) AS rev_2024,
SUM(CASE WHEN year = '2025' THEN revenue ELSE 0 END) AS rev_2025
FROM dept_year
GROUP BY department One SUM(CASE …) per new column. Unpivot back with UNION ALL.
Gaps and islands Lesson 8.4
WITH d AS (
SELECT DISTINCT order_date AS day FROM orders WHERE customer_id = 17
), g AS (
SELECT day, julianday(day) - ROW_NUMBER() OVER (ORDER BY day) AS grp FROM d
)
SELECT MIN(day) AS start, MAX(day) AS finish, COUNT(*) AS days
FROM g GROUP BY grp Date minus row number is constant inside a streak.
Cohort retention Lesson 8.2
WITH firsts AS (
SELECT customer_id, MIN(strftime('%Y-%m', order_date)) AS cohort
FROM orders GROUP BY customer_id
)
SELECT f.cohort,
(CAST(strftime('%Y', o.order_date) AS INTEGER) - CAST(substr(f.cohort, 1, 4) AS INTEGER)) * 12
+ CAST(strftime('%m', o.order_date) AS INTEGER) - CAST(substr(f.cohort, 6, 2) AS INTEGER) AS month_no,
COUNT(DISTINCT o.customer_id) AS active
FROM orders o JOIN firsts f USING (customer_id)
GROUP BY f.cohort, month_no Divide each cell by month 0 of its cohort for the retention rate.
Days with no sales still showing Lesson 5.5
WITH RECURSIVE cal(d) AS (…) -- the recursive CTE above
SELECT cal.d, COUNT(o.order_id) AS orders
FROM cal LEFT JOIN orders o ON o.order_date = cal.d
GROUP BY cal.d Count a right-side column, so empty days give 0, not 1.
Changing data and speed
Change data safely Lesson 9.5
BEGIN;
UPDATE products SET unit_price = unit_price * 1.05 WHERE category_id = 10;
SELECT COUNT(*) FROM products WHERE category_id = 10; -- check
COMMIT; -- or ROLLBACK; to undo Run the matching SELECT first. No WHERE means every row.
Insert, delete, upsert Lesson 9.5
INSERT INTO t (a, b) VALUES (1, 'x');
DELETE FROM t WHERE a = 1;
INSERT INTO t (a, b) VALUES (1, 'y') ON CONFLICT (a) DO UPDATE SET b = excluded.b; Upsert: SQLite and PostgreSQL use ON CONFLICT; SQL Server uses MERGE.
Speed Lesson 9.2
EXPLAIN QUERY PLAN SELECT … ; -- SCAN = whole table, SEARCH = index
CREATE INDEX idx_orders_store_date ON orders (store_id, order_date); Equality column first, range column last. Keep filters sargable: no function around the column.