Postgres in depth · Chapter 24 of 65
The signup funnel
By the end of this chapter you can break a long question into named steps with WITH, reuse a step, and decide whether Postgres computes a step on its own or folds it into the rest. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.
- Part: Subqueries, CTEs and windows
- 4 tasks
- PostgreSQL
- In the all labs plan
The first 2 chapters of Postgres in depth are free. This one comes with the all labs plan.
Why you need this
Arjun's board meeting is on Friday and he wants LCO's funnel: of the students who signed up this year, how many enrolled, how many started a lesson, how many finished a course. Each number is a subquery over the same list of signups. Written with nested brackets, the query is four copies of the same idea, inside out, and nobody can review it.
Chapter 23 put questions inside questions. This chapter puts them one after another, each with a name, so the query reads top to bottom like the steps you would explain to Arjun.
What you build: 4 tasks
Each task has tests. You write the code in the lab's editor and press Check.
- Task 1
funnel.sql: the 2026 signup funnel
Arjun wants LCO's funnel for the students who signed up in 2026 (on or after 2026-01-01, India time): how many signed up, how many of them enrolled in a course, how many started a lesson (have any lesson progress), and how many finished a course (have an enrollment with completed_at).
Return four rows with four columns: step (1 to 4), stage (exactly 'signed up', 'enrolled', 'started a lesson', 'finished a course'), students (how many of the 2026 signups reached that stage) and pct (students as a percentage of the 2026 signups, rounded to 1 decimal). Order by step.
Careful: the edge data has a student who signed up at 2025-12-31 23:30; she is not part of the 2026 funnel, and nor is anything she did.
- Task 2
big-spenders.sql: students who spent more than the average buyer
For each student with at least one paid order, their total spent is the sum of the total of their paid orders. Kabir wants the students whose total spent is above the average total spent of those students.
Return two columns: name and total_spent. Order by total_spent from highest to lowest, then name.
Careful: the average is over the students who have a paid order (a free order still counts, at 0), not over every student.
- Task 3
completions.sql: lesson completions, computed once
Priya is comparing plans. Write a CTE named completions that counts, for every lesson in LCO, how many students completed it (lesson_progress rows with completed_at). Mark it so that Postgres computes it on its own, as written, and does not fold it into the main query. The main query then lists the lessons of SQL Fundamentals with their count.
Return four columns: section (the section's position), position (the lesson's position), title and completed (0 for a lesson nobody completed). Order by section, then position.
The Check also reads your query's plan: it must show the CTE scanned as its own step.
- Task 4 · interview question
Interview: repeat-buyers.sql, bought again within 30 days
A buyer is a student with at least one paid order. A repeat buyer is a buyer who has another paid order paid within 30 days after their first paid order (paid_at later than the first, and at most 30 days after it).
Return one row with three columns: buyers, repeat_buyers and repeat_pct (repeat buyers as a percentage of buyers, rounded to 1 decimal).
Careful: only paid orders count, and both the first order and the next one are measured by paid_at. A failed or refunded order in between is not a purchase.
What you will learn
- CTEs: WITH name AS (...), a named query for one statement.
- Chains of CTEs: a step that reads the steps before it.
- Reading a CTE twice: one computation, used for the rows and for their average.
- MATERIALIZED and NOT MATERIALIZED: when Postgres computes a CTE on its own, and why that changes the plan.
More in Subqueries, CTEs and windows
Build The signup funnel yourself
LCO, an edtech company, moves its platform onto a single Postgres server you run. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.
The first 2 chapters of Postgres in depth are free. This one comes with the all labs plan.