Advanced SQL: window functions and performance

01

Before you start, and CTEs: naming the steps of a query

In simple words

When you cook a big meal, you prepare small bowls first: chopped onions, washed rice, mixed sauce. Each bowl has a name. Then you combine them. A CTE is a small named bowl inside one query.

Before this course: SQL Joins & Analysis (course 7.4.2). You should already know joins, LEFT JOIN, GROUP BY with HAVING, CASE, subqueries and views. This course uses the same four-table shop and the order_lines view from that course, so build them first (course 7.4.2, Lessons 1 and 6). Here we go one step deeper: window functions, CTEs, indexes and query plans.

Key facts

A CTE (common table expression) is a named SELECT written at the top of a query with WITH name AS (...). The main query can then use name like a table.
You can write several CTEs in one WITH, separated by commas. A later CTE can use an earlier one.
A CTE only exists while that one query runs. A view (course 7.4.2) is saved in the database; a CTE is not.
CTEs make long queries readable: each step has a name and you can test each step alone.
A recursive CTE refers to itself. It has two parts joined by UNION ALL: a starting SELECT (the first rows) and a recursive SELECT that adds new rows from the rows found so far. It stops when the recursive part returns no new rows.
Always give a recursive CTE a clear stop condition (a WHERE such as x < 5). SQLite's documentation also suggests adding a LIMIT as a safety net when you know the maximum size.
Differs between databases:

Database	Recursive CTE keyword	Note
SQLite	`WITH RECURSIVE` (SQLite does not force the keyword)	MATERIALIZED / NOT MATERIALIZED hints from version 3.35.0
PostgreSQL	`WITH RECURSIVE`	A CTE used only once is normally folded into the main query; `MATERIALIZED` forces it to be computed separately
SQL Server	plain `WITH` (no RECURSIVE keyword)	Default recursion limit is 100 levels; change it with `OPTION (MAXRECURSION n)`

Example (the long query from course 7.4.2, now short)

Customers who spent more than the average customer (all orders, as in course 7.4.2):

WITH customer_totals AS (
 SELECT customer_id, customer, SUM(line_total) AS total
 FROM order_lines
 GROUP BY customer_id, customer
)
SELECT customer, total
FROM customer_totals
WHERE total > (SELECT AVG(total) FROM customer_totals)
ORDER BY total DESC;

Result: Ana 132.0, Dev 98.0. The CTE is written once and used twice.

Example (two CTEs in a chain: paid revenue per month)

WITH paid AS (
 SELECT * FROM order_lines WHERE status = 'paid'
),
by_month AS (
 SELECT strftime('%Y-%m', order_date) AS month,
     COUNT(DISTINCT order_id) AS orders,
     SUM(line_total) AS revenue
 FROM paid
 GROUP BY month
)
SELECT month, orders, revenue, ROUND(revenue / orders, 2) AS avg_order
FROM by_month
ORDER BY month;

Result: 2026-09: 1 order, 33.0, 33.0; 2026-10: 3, 156.5, 52.17; 2026-11: 2, 113.0, 56.5.

Example (recursive: who joined through Ana's referrals?)

WITH RECURSIVE chain(id, name, level) AS (
 SELECT id, name, 0 FROM customers WHERE id = 1
 UNION ALL
 SELECT c.id, c.name, chain.level + 1
 FROM customers c
 JOIN chain ON c.referred_by = chain.id
)
SELECT name, level FROM chain ORDER BY level, name;

Result: Ana 0, Ben 1, Chloe 1, Dev 2. Dev was referred by Ben, who was referred by Ana.

Example (recursive: a list of months, so empty months show 0)

WITH RECURSIVE months(m) AS (
 SELECT '2026-08-01'
 UNION ALL
 SELECT date(m, '+1 month') FROM months WHERE m < '2026-12-01'
)
SELECT strftime('%Y-%m', months.m) AS month,
    COALESCE(SUM(ol.line_total), 0) AS revenue
FROM months
LEFT JOIN order_lines ol
 ON strftime('%Y-%m', ol.order_date) = strftime('%Y-%m', months.m)
 AND ol.status = 'paid'
GROUP BY months.m
ORDER BY months.m;

Result: 2026-08: 0, 2026-09: 33.0, 2026-10: 156.5, 2026-11: 113.0, 2026-12: 0. Without the month list, August and December would be missing, and a chart would hide the empty months.

Step by step

Open your shop2.db from course 7.4.2 (or rebuild the four tables and the order_lines view).
Run only the inner SELECT of customer_totals first. Check: 4 rows (Ana, Ben, Chloe, Dev).
Wrap it in WITH customer_totals AS (...) and add the main SELECT. Check: Ana and Dev.
Run the two-CTE monthly query. Then change the last line to SELECT * FROM paid; to look inside the first step.
Run WITH RECURSIVE n(x) AS (SELECT 1 UNION ALL SELECT x + 1 FROM n WHERE x < 5) SELECT x FROM n; and check you get 1 to 5.
Run the referral chain query and draw the chain on paper: Ana, then Ben and Chloe, then Dev.
Run the month-list query. Check: five months, with 0 for August and December.

Watch on YouTube

A recursive CTE explained with a simple exampleThe Code Samples · YouTube · youtube.com

Videos by other creators on YouTube, for reference. They are not made by or affiliated with The Future Market.

LESSON 01Before you start, and CTEs: naming the steps of a query

LESSON 01 / ANIMATED LESSON / AI VOICE00:00 / 01:15

Voice

Read the video script

[Host] "Long queries are hard to read. CTEs let you name each step."
[Screen: WITH customer_totals AS ( ... ) highlighted] "Write the step once, give it a name, use it like a table. Here we use it twice."
[Screen: referral chain Ana to Ben to Dev] "A recursive CTE repeats a step until nothing new is found. That's how you walk a chain of referrals."
[Text on screen: always add a stop condition] "Always give it a way to stop, like x less than 5."

Try it now

Write a CTE city_revenue with paid revenue per city (from order_lines), then show only the cities above the average city revenue. Check: Toronto 230.0 is above the average; Hamilton 72.5 is not.
Write a recursive CTE that lists every day from 2026-11-01 to 2026-11-07. Check: 7 rows.

ClaudeStuck? Copy a ready-made prompt for Claude

Copy this, open Claude, paste it, and Claude will walk you through the lesson step by step.

For a grown-up: Claude is for adults (18+). Copy this, paste it into Claude, and use the answer to explain the lesson to your child.

I'm learning "Advanced SQL: window functions and performance" on The Future Market. Lesson 1: "Before you start, and CTEs: naming the steps of a query".

What the lesson teaches:
- A CTE (common table expression) is a named SELECT written at the top of a query with WITH name AS (...). The main query can then use name like a table.
- You can write several CTEs in one WITH, separated by commas. A later CTE can use an earlier one.
- A CTE only exists while that one query runs. A view (course 7.4.2) is saved in the database; a CTE is not.
- CTEs make long queries readable: each step has a name and you can test each step alone.
- A recursive CTE refers to itself. It has two parts joined by UNION ALL: a starting SELECT (the first rows) and a recursive SELECT that adds new rows from the rows found so far. It stops when the recursive part returns no new rows.
- Always give a recursive CTE a clear stop condition (a WHERE such as x < 5). SQLite's documentation also suggests adding a LIMIT as a safety net when you know the maximum size.
- Differs between databases:

The exercise I'm working on:
1. Write a CTE city_revenue with paid revenue per city (from order_lines), then show only the cities above the average city revenue. Check: Toronto 230.0 is above the average; Hamilton 72.5 is not. 2. Write a recursive CTE that lists every day from 2026-11-01 to 2026-11-07. Check: 7 rows.

I'm stuck. Please:
1. Explain the main idea in plain, simple words with one everyday example.
2. Walk me through the exercise one small step at a time. Ask me what I've done so far before giving the next step.
3. Don't just give me the final answer. Help me do it myself, and check my work when I share it.

I'm a parent helping my child (about 8 years old) with a lesson called "Before you start, and CTEs: naming the steps of a query" from the course "Advanced SQL: window functions and performance".

Here's what the lesson says:
When you cook a big meal, you prepare small bowls first: chopped onions, washed rice, mixed sauce. Each bowl has a name. Then you combine them. A CTE is a small named bowl inside one query.

The activity:
1. Write a CTE city_revenue with paid revenue per city (from order_lines), then show only the cities above the average city revenue. Check: Toronto 230.0 is above the average; Hamilton 72.5 is not. 2. Write a recursive CTE that lists every day from 2026-11-01 to 2026-11-07. Check: 7 rows.

Please explain this so an 8-year-old understands: short sentences, a fun everyday example, and no hard words.
Then give me 3 simple questions I can ask my child to check they understood, and a way to do the activity together, one tiny step at a time.

Open Claude

Sources

SQLite, the WITH clause (CTEs, recursive CTEs, MATERIALIZED hints, LIMIT advice): www.sqlite.org/lang_with.html
PostgreSQL documentation, WITH queries: www.postgresql.org/docs/current/queries-with.html
Microsoft Learn, WITH common_table_expression (Transact-SQL): learn.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expressi

My notes

I learned this

02