This page needs a newer browser to run its tools. You can still read everything here.

SQL (PostgreSQL, MySQL, SQLite) Module 7

Common table expressions and recursive queries interview questions

Interview questions on common table expressions and recursive queries: warm-ups, then what each harder question tests and how a good answer starts.

16 questions: 5 warm-ups and 11 with model answers · Levels: Fresher to staff

Warm-up questions

Short questions with short answers, each answered in full.

Fresher to mid

What is a common table expression, and how is it different from a subquery in FROM?

Answered in the lesson WITH and common table expressions (CTEs)
Mid

Is a CTE computed once, or once for every place that reads it?

Answered in the lesson WITH and common table expressions (CTEs)
Fresher to mid

What are the anchor and the recursive member of a recursive CTE, and what makes the recursion stop?

Answered in the lesson Recursive CTEs, series and calendar tables
Fresher to mid

How do you store a tree such as an org chart in one table, and how do you list everyone under a manager at any depth?

Answered in the lesson Querying hierarchies: org charts and trees
Fresher to mid

Why does a recursive CTE that walks a graph need a visited check when one that walks a tree usually does not, and how do you write it?

Answered in the lesson Graph problems in SQL: routes and reachability

Questions with model answers

What each question tests, the first step of a good answer and the follow-ups to expect.

Mid to senior

A colleague splits a PostgreSQL 16 report into five CTEs and marks each one AS MATERIALIZED, "so every step runs once". The report goes from 300 milliseconds to 14 seconds, although the tables have the right indexes. What happened, and what do you change?
What it tests

How PostgreSQL decides between folding and materialising a CTE, materialisation as an optimisation fence, and reading plans before and after a rewrite.

First step

Compare EXPLAIN (ANALYZE, BUFFERS) of both versions. A materialised CTE is computed in full before the outer query's conditions can reach it, so a step that used to fetch a few hundred rows through an index now scans and stores whole tables. Look for CTE Scan nodes whose row counts are far larger than the final result.
Follow-ups
- Which of the five steps, if any, should stay materialised?
- The same report must also run on PostgreSQL 11. What changes?
- How do you show the team that the fix returns the same rows?
From the lesson WITH and common table expressions (CTEs)
Model answer, Pro. Model answers open with any pass; prices are in the notice on this page.
Opening the model answers with your pass…
Model answers open with any MySmartCoPilot pass.
Pro pass · 1 week₹79$2.99
30 days₹179$5.99
1 year₹1,699$45
One payment; a pass never renews. The price shown is the total you pay: no fees or charges are added at checkout.
Passes can’t be bought right now, so the model answers can’t be opened at the moment. If you have a pass, enter your licence key.
Choose a pass I have a licence key
It also opens every other in-depth part in Learn and every Pro tool. Sales are final, except where the law gives you a right to withdraw, for example in the EU and the UK (refund policy).
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Mid to senior

Move every order older than two years, about 3 million of 40 million rows, from orders to orders_archive while the shop keeps taking orders, without losing or duplicating a row. How do you do it in PostgreSQL, and how in MySQL?
What it tests

Data-modifying CTEs and RETURNING, transactions and snapshots, moving rows in batches, and checking afterwards that the counts add up.

First step

Fix the cutoff once as a constant, not now() in each statement, so every batch uses the same boundary. In PostgreSQL one statement moves a batch: a CTE deletes up to 10,000 old rows with RETURNING *, and the INSERT reads exactly those rows. Repeat it until a batch moves nothing.
Follow-ups
- Why not move all 3 million rows in one statement?
- MySQL has no data-modifying CTEs. What do you write there?
- How do you prove afterwards that no row was lost or copied twice?
From the lesson WITH and common table expressions (CTEs)
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Mid to senior

A nightly job builds a daily calendar with WITH RECURSIVE. It runs on SQLite and PostgreSQL, but on MySQL 8.4 it fails with error 3636 since the range was extended to five years. Explain the error, and give two fixes with their trade-offs.
What it tests

How recursion depth relates to rows in a series, MySQL's cte_max_recursion_depth, and generating rows without recursion or storing them once.

First step

Count the levels: one day per pass means five years of days need about 1,826 passes after the anchor, and MySQL stops any recursive CTE after cte_max_recursion_depth levels, 1,000 by default. The query is correct; the guard that protects the server from runaway recursion stopped it.
Follow-ups
- Why does the same query never fail on PostgreSQL or SQLite?
- Which fix would you choose for a reporting database used by many teams?
- The calendar must also hold every financial quarter. What do you store?
From the lesson Recursive CTEs, series and calendar tables
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Fresher to mid

A daily revenue chart for a 30-day month shows only 23 points. After you add a calendar and a LEFT JOIN, all 30 days appear, but the quiet days show 1 order each instead of 0. Find both bugs and write the corrected query.
What it tests

Why grouped data has no rows for empty days, LEFT JOIN with a calendar, and which count and sum give 0 rather than 1 or NULL.

First step

The first bug is that a GROUP BY over sales can only make rows for days that have sales: 7 days had none. The second is count(): the LEFT JOIN keeps one row for each quiet day, with NULLs on the sales side, and count() counts that row. Count a column of the sales table instead.
Follow-ups
- The filter on the sales status is in the WHERE clause. What happens to the quiet days?
- Where should the calendar come from in production?
- How would you test the query so that the bug cannot return?
From the lesson Recursive CTEs, series and calendar tables
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Mid to senior

Billing runs on the last day of every month. A series of billing dates built by adding one month to the previous date, starting from the last day of January, is wrong in SQLite, PostgreSQL and MySQL, in different ways. What does each engine produce, and how do you build the series so that all three agree?
What it tests

How each engine resolves a month added to a day that does not exist, why chained additions drift, and computing every date from a fixed start.

First step

Before writing any SQL, work out the first three dates by hand from each engine's documented rule for a day that does not exist in the next month. Then look at where each date comes from: from the previous row, or from the first date of the series.
Follow-ups
- What does the billing series look like for a customer who signed up on the 30th?
- How do you write the last day of a month in each engine?
- How would you test the series before it bills anyone?
From the lesson Recursive CTEs, series and calendar tables
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Mid to senior

After an HR import, the query that shows each employee's chain of managers never finishes for some employees, and the head count computed from the top of the company dropped by 37. What happened, how do you make both queries safe, and how do you stop it from happening again?
What it tests

Recognising a cycle in an adjacency list from two symptoms, stopping a walk with a path check or the CYCLE clause, and preventing cycles with a check on write.

First step

Both symptoms point to a cycle. A walk up from someone inside a loop of managers never reaches the top, and a walk down from the top never reaches the people who hang from that loop, so they drop out of the count. Confirm it by finding the employees whose chain of managers never reaches the person at the top.
Follow-ups
- Why did the foreign key not prevent this?
- How do you find every cycle in a table of 80,000 employees?
- Where do you enforce "no cycles" so that the next import fails instead?
From the lesson Querying hierarchies: org charts and trees
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Senior to staff

A dashboard shows every manager's whole team size and monthly salary cost for a company of 50,000 people, and it must load in under a second. Write the query, estimate what it costs, and decide whether to store anything.
What it tests

Turning a hierarchy into manager and member pairs, estimating their number from the shape of the tree, and the choice between computing on read and keeping a closure table.

First step

Make one row per manager and per person below them with a recursive CTE whose anchor pairs everyone with themselves, then GROUP BY the manager. The number of pairs is the sum of every person's depth plus one, so estimate the depth of the tree before you compare the cost with the one-second budget.
Follow-ups
- The tree is a long chain in one department. What happens to the estimate?
- How do you keep a closure table correct when someone changes manager?
- The dashboard shows each manager's own team only. Does that change the design?
From the lesson Querying hierarchies: org charts and trees
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Mid to senior

Print an org chart in which every manager appears directly above their team, with each team in the order people joined, in PostgreSQL and in SQLite. Why does sorting by a path of ids written as text fail, and what do you use instead?
What it tests

Depth-first order from a sort key, text against numeric comparison, and the ordering features of PostgreSQL and SQLite.

First step

Carry a path from the top in the recursive CTE and sort the final result by it. As text, '/1/10' sorts before '/1/2' because the character 1 is smaller than 2, so a team whose manager has id 10 jumps ahead. The key has to compare the way numbers do.
Follow-ups
- What does PostgreSQL's SEARCH clause give you that a hand-made path does not?
- The chart must be sorted by name within each team instead. What changes?
- How deep and wide can the tree grow before the padded key breaks?
From the lesson Querying hierarchies: org charts and trees
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Senior to staff

A journey planner finds the fewest changes between two stations with a recursive CTE that walks every route. It answers at once on a test network of 40 stations and runs for minutes on the real one, with 7,000 stations and 18,000 links. Explain why, and redesign it.
What it tests

The difference between routes and nodes, why a recursive CTE cannot keep only the first arrival, breadth-first search with a visited table, and caps that follow from the data.

First step

The walk keeps one row per route, not per station. With about five links per station, the routes multiply with every extra hop while the stations do not. Ask what the largest number of changes in a real answer is, then stop enumerating routes.
Follow-ups
- Why not add a GROUP BY or a min() inside the recursive step?
- How many rounds does the visited-table search need, and what does each round cost?
- The planner must also return the route itself, not only the count. What changes?
From the lesson Graph problems in SQL: routes and reachability
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Senior to staff

The planner now needs the fastest journey in minutes, not the fewest changes. Can you compute it in SQL? How would you do it, what does it cost, and when would you move it out of the database?
What it tests

Weighted shortest paths, why enumerating routes explodes, Bellman-Ford as rounds of set-based updates, Dijkstra's need for a priority queue, and choosing a routing tool.

First step

Separate correctness from cost. Walking every route and keeping the smallest total is correct, but its cost follows the number of routes. A set-based method relaxes every link in rounds instead: in each round, a station takes the best time through any neighbour that already has one.
Follow-ups
- How do you know when to stop the rounds early?
- What changes if some links have a negative cost, such as a refund?
- How would you return the route, not only the time?
From the lesson Graph problems in SQL: routes and reachability
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Mid to senior

Suggest people a user may know: everyone two hops away in a friendships table who is not the user and not already a friend, ranked by the number of mutual friends. Write the query and say what it costs for a user with 2,000 friends.
What it tests

Two-hop queries without recursion, excluding existing links, counting mutual friends, and estimating the rows behind a fan-out.

First step

Two hops need no recursion: join friendships to itself, from the user to each friend and from each friend to their friends. Then remove the user and the existing friends with NOT EXISTS, and GROUP BY the candidate, counting the friends that lead to them as mutual friends.
Follow-ups
- Friendships are stored once per pair, in either column. How does that change the query?
- How many rows does the join make for a user with 2,000 friends who have 300 friends each?
- The page must load in 50 milliseconds. What do you precompute?
From the lesson Graph problems in SQL: routes and reachability
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes

The lessons of this module

WITH and common table expressions (CTEs) Free lesson, Pro answers: 25 minutes
Recursive CTEs, series and calendar tables Free core, Pro depth: 10 of 35 minutes free
Querying hierarchies: org charts and trees Free core, Pro depth: 13 of 35 minutes free
Graph problems in SQL: routes and reachability Free core, Pro depth: 13 of 35 minutes free

All SQL (PostgreSQL, MySQL, SQLite) interview questions · The SQL (PostgreSQL, MySQL, SQLite) track

Warm-up questions

What is a common table expression, and how is it different from a subquery in FROM?

Is a CTE computed once, or once for every place that reads it?

What are the anchor and the recursive member of a recursive CTE, and what makes the recursion stop?

How do you store a tree such as an org chart in one table, and how do you list everyone under a manager at any depth?

Why does a recursive CTE that walks a graph need a visited check when one that walks a tree usually does not, and how do you write it?

Questions with model answers

A colleague splits a PostgreSQL 16 report into five CTEs and marks each one AS MATERIALIZED, "so every step runs once". The report goes from 300 milliseconds to 14 seconds, although the tables have the right indexes. What happened, and what do you change?

Move every order older than two years, about 3 million of 40 million rows, from orders to orders_archive while the shop keeps taking orders, without losing or duplicating a row. How do you do it in PostgreSQL, and how in MySQL?

A nightly job builds a daily calendar with WITH RECURSIVE. It runs on SQLite and PostgreSQL, but on MySQL 8.4 it fails with error 3636 since the range was extended to five years. Explain the error, and give two fixes with their trade-offs.

A daily revenue chart for a 30-day month shows only 23 points. After you add a calendar and a LEFT JOIN, all 30 days appear, but the quiet days show 1 order each instead of 0. Find both bugs and write the corrected query.

After an HR import, the query that shows each employee's chain of managers never finishes for some employees, and the head count computed from the top of the company dropped by 37. What happened, how do you make both queries safe, and how do you stop it from happening again?

A dashboard shows every manager's whole team size and monthly salary cost for a company of 50,000 people, and it must load in under a second. Write the query, estimate what it costs, and decide whether to store anything.

Print an org chart in which every manager appears directly above their team, with each team in the order people joined, in PostgreSQL and in SQLite. Why does sorting by a path of ids written as text fail, and what do you use instead?

A journey planner finds the fewest changes between two stations with a recursive CTE that walks every route. It answers at once on a test network of 40 stations and runs for minutes on the real one, with 7,000 stations and 18,000 links. Explain why, and redesign it.

The planner now needs the fastest journey in minutes, not the fewest changes. Can you compute it in SQL? How would you do it, what does it cost, and when would you move it out of the database?

Suggest people a user may know: everyone two hops away in a friendships table who is not the user and not already a friend, ranked by the number of mutual friends. Write the query and say what it costs for a user with 2,000 friends.