Chapter 19 Advanced 60 Questions

Practice Questions — Common Table Expressions (CTEs) and Recursive Queries

← Back to Notes

7 Easy

13 Medium

10 Hard

Topic-Specific Questions

Question 1

Easy

Which keyword introduces a CTE?

It's a single word that comes before the name.

WITH

Question 2

Easy

What extra keyword is needed for a CTE that references itself?

Comes after WITH.

RECURSIVE

Question 3

Easy

What does this query return?

WITH RECURSIVE n AS (
  SELECT 1 AS x UNION ALL
  SELECT x + 1 FROM n WHERE x < 5
)
SELECT * FROM n;

Trace iterations.

1, 2, 3, 4, 5

Question 4

Easy

What minimum MySQL version supports CTEs?

Major version release that brought modern SQL features.

8.0

Question 5

Medium

What happens if you omit the termination condition in a recursive CTE?

MySQL has a safety net.

MySQL aborts after cte_max_recursion_depth iterations (default 1000) with an error.

Question 6

Medium

Is this legal?

WITH a AS (SELECT 1 AS x),
     b AS (SELECT x + 1 FROM a)
SELECT * FROM b;

Later CTEs can see earlier CTEs.

Yes — b references a, returns 2.

Question 7

Medium

In a recursive CTE, the two parts are separated by which operator?

Union, but which kind?

UNION ALL

Question 8

Medium

Given an orders table, what does this query return?

WITH paid AS (
  SELECT customer_id, SUM(amount) AS t FROM orders
  WHERE status='paid' GROUP BY customer_id
)
SELECT COUNT(*) FROM paid WHERE t > 1000;

Count customers whose paid total exceeds 1000.

The count of customers whose paid total exceeds 1000.

Question 9

Hard

What does the recursive member of a CTE reference?

Not the original table.

The CTE itself (the accumulated results so far).

Question 10

Hard

Why CAST(0 AS SIGNED) in the anchor of a recursive CTE?

Column type consistency.

To ensure the column type and size match the recursive member, preventing silent truncation or type errors.

Question 11

Medium

What's the main advantage of a CTE over a derived table in FROM?

Think about what you can do with a named thing vs an anonymous one.

A CTE is named and can be referenced multiple times in the same query. A derived table is anonymous and can only be referenced where it is declared. CTEs also support recursion, which derived tables do not.

Question 12

Medium

Can you reference a CTE after the statement that declares it?

Scope.

No — a CTE exists only for the duration of the statement that declares it. For cross-statement reuse, create a VIEW (persistent) or a TEMPORARY TABLE (session-scoped).

Question 13

Hard

When should you use a recursive CTE vs an application-side loop?

Consider network roundtrips and locking.

Use a recursive CTE when: (1) the logic is fundamentally hierarchical/graph-like, (2) the recursion depth is bounded, (3) avoiding multiple database roundtrips matters. Use an application loop when: (1) the depth is unbounded or user-dependent, (2) you need to run complex business logic between iterations, (3) memory pressure in the database is a concern.

Question 14

Easy

Use a CTE to return customers with total paid amount > 5000 from orders(customer_id, amount, status).

Aggregate inside a CTE, filter in the outer query.

WITH totals AS (
  SELECT customer_id, SUM(amount) AS total
  FROM orders
  WHERE status = 'paid'
  GROUP BY customer_id
)
SELECT customer_id, total
FROM totals
WHERE total > 5000
ORDER BY total DESC;

Question 15

Medium

Write a recursive CTE that generates integers from 1 to 20.

Anchor returns 1, recursive adds 1 until 20.

WITH RECURSIVE nums AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM nums WHERE n < 20
)
SELECT n FROM nums;

Question 16

Medium

Write a recursive CTE that returns the 30 most recent dates ending today.

Start at today, decrement by 1 day.

WITH RECURSIVE dates AS (
  SELECT CURDATE() AS d
  UNION ALL
  SELECT d - INTERVAL 1 DAY
  FROM dates
  WHERE d > CURDATE() - INTERVAL 29 DAY
)
SELECT d FROM dates ORDER BY d;

Question 17

Hard

Given employees(id, name, manager_id), write a CTE that returns every employee's direct and indirect manager chain as a single concatenated string.

Recursive CTE starting from root, concat path.

WITH RECURSIVE org AS (
  SELECT id, name, manager_id, CAST(name AS CHAR(1000)) AS chain
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, CONCAT(o.chain, ' > ', e.name)
  FROM employees e
  JOIN org o ON e.manager_id = o.id
)
SELECT id, name, chain FROM org ORDER BY chain;

Question 18

Hard

Rewrite this nested subquery using CTEs:

SELECT customer_id, total
FROM (SELECT customer_id, SUM(amount) AS total
      FROM orders GROUP BY customer_id) t
WHERE total > (SELECT AVG(total) FROM
      (SELECT customer_id, SUM(amount) AS total
       FROM orders GROUP BY customer_id) t2);

Define the aggregation once as a CTE, reference it twice.

WITH customer_totals AS (
  SELECT customer_id, SUM(amount) AS total
  FROM orders
  GROUP BY customer_id
)
SELECT customer_id, total
FROM customer_totals
WHERE total > (SELECT AVG(total) FROM customer_totals);

Question 19

Hard

Use a CTE combined with a window function to return each customer's name, order count, and rank by count.

First CTE: counts per customer. Then window RANK.

WITH customer_orders AS (
  SELECT customer_id, COUNT(*) AS order_count
  FROM orders
  GROUP BY customer_id
)
SELECT customer_id, order_count,
       RANK() OVER (ORDER BY order_count DESC) AS rnk
FROM customer_orders;

Question 20

Medium

Use a recursive CTE to compute the factorial of 10.

Anchor: 1! = 1. Recursive: n! = n * (n-1)!.

WITH RECURSIVE f AS (
  SELECT 1 AS n, 1 AS fact
  UNION ALL
  SELECT n + 1, (n + 1) * fact
  FROM f
  WHERE n < 10
)
SELECT n, fact FROM f;

Mixed & Application Questions

Question 1

Easy

Is this legal?

WITH a AS (SELECT 1) SELECT * FROM a;

Is SELECT 1 a valid SELECT?

Yes. Returns a single row with column value 1.

Question 2

Easy

What does this return?

WITH RECURSIVE r AS (
  SELECT 0 AS n UNION ALL
  SELECT n + 2 FROM r WHERE n < 10
)
SELECT COUNT(*) FROM r;

Count the even numbers from 0 to 10.

6

Question 3

Medium

Can a CTE be referenced outside the statement that declares it?

Scope.

No. A CTE is scoped to the single statement.

Question 4

Medium

Given a CTE returning 5 rows, and you reference it 3 times in the outer query, how many times does MySQL evaluate it?

The optimizer decides — may be once or multiple times.

It depends — MySQL may inline the CTE (evaluating once per reference) or materialize it (evaluate once, reuse). MySQL 8.0 generally inlines non-recursive CTEs.

Question 5

Medium

Use a CTE to find customers who placed more than 3 orders AND have total spend > 5000 from orders(customer_id, amount).

CTE returns per-customer count and sum; outer query filters both.

WITH summary AS (
  SELECT customer_id, COUNT(*) AS cnt, SUM(amount) AS total
  FROM orders
  GROUP BY customer_id
)
SELECT customer_id, cnt, total
FROM summary
WHERE cnt > 3 AND total > 5000;

Question 6

Hard

Use a recursive CTE to print the hierarchy of categories from categories(id, name, parent_id).

Root categories have parent_id IS NULL.

WITH RECURSIVE cat_tree AS (
  SELECT id, name, parent_id, 0 AS depth,
         CAST(name AS CHAR(500)) AS path
  FROM categories
  WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.name, c.parent_id, t.depth + 1,
         CONCAT(t.path, ' / ', c.name)
  FROM categories c
  JOIN cat_tree t ON c.parent_id = t.id
)
SELECT CONCAT(REPEAT('  ', depth), name) AS tree, path
FROM cat_tree
ORDER BY path;

Question 7

Hard

If a recursive CTE's anchor has column name VARCHAR(20) but CONCAT in the recursive step produces longer strings, what happens?

Anchor fixes the type.

Strings are silently truncated to 20 characters.

Question 8

Hard

Use CTEs to find the top 3 customers by paid revenue, including each customer's percentage of the grand total.

Two CTEs: per-customer totals, plus the grand total.

WITH totals AS (
  SELECT customer_id, SUM(amount) AS t
  FROM orders WHERE status='paid' GROUP BY customer_id
),
grand AS (
  SELECT SUM(t) AS all_total FROM totals
)
SELECT t1.customer_id, t1.t,
       ROUND(100.0 * t1.t / g.all_total, 2) AS pct_of_total
FROM totals t1 CROSS JOIN grand g
ORDER BY t1.t DESC
LIMIT 3;

Question 9

Medium

Why is UNION ALL required (not UNION) in recursive CTEs?

Performance and correctness.

UNION removes duplicates, which would require checking every new row against the entire accumulated set — very expensive. UNION ALL blindly appends, which is O(1) per row. Also, correctness: deduplication could prematurely drop rows if duplicates are meaningful (e.g., counting paths).

Question 10

Hard

When would you prefer a TEMPORARY TABLE over a CTE?

Think about multi-statement reuse and complex logic.

Use a TEMPORARY TABLE when: (1) you need the result across multiple statements in the same session, (2) the CTE is expensive and you need guaranteed materialization (avoid re-evaluation), (3) you want to add indexes to the intermediate result. Use CTEs when: (1) logic is statement-local, (2) recursion is needed, (3) readability is the goal.

Multiple Choice Questions

MCQ 1

Which keyword starts a Common Table Expression?

A. WITH
B. CTE
C. USING
D. CREATE

Answer: A
A is correct. WITH cte_name AS (...) is the syntax in MySQL and standard SQL.

MCQ 2

What extra keyword is needed when a CTE references itself?

A. SELF
B. REPEAT
C. RECURSIVE
D. LOOP

Answer: C
C is correct. WITH RECURSIVE cte AS (...). Without RECURSIVE, MySQL treats the self-reference as an unknown table.

MCQ 3

What is the scope of a CTE?

A. Just the outer SELECT
B. The entire statement that declares it
C. The whole session
D. Persistent until DROP

Answer: B
B is correct. A CTE lives for the duration of one statement. Use a VIEW or TEMPORARY TABLE for broader scope.

MCQ 4

Which operator separates anchor and recursive members?

A. UNION
B. UNION ALL
C. INTERSECT
D. JOIN

Answer: B
B is correct. Recursive CTEs require UNION ALL, not UNION. UNION would deduplicate and is much slower/unsafe.

MCQ 5

Which MySQL version introduced CTEs?

A. 5.6
B. 5.7
C. 8.0
D. 5.5

Answer: C
C is correct. MySQL 8.0 brought CTEs and window functions together.

MCQ 6

How do you declare multiple CTEs in one query?

A. Separate them with semicolons
B. Use WITH keyword for each
C. Separate them with commas
D. Use AND

Answer: C
C is correct. WITH a AS (...), b AS (...) SELECT .... One WITH, comma-separated CTEs.

MCQ 7

What is the default value of cte_max_recursion_depth in MySQL 8.0?

A. 100
B. 1000
C. 10000
D. Unlimited

Answer: B
B is correct. Default is 1000 iterations. Raise with SET @@cte_max_recursion_depth = 10000; when truly needed.

MCQ 8

Can a CTE reference a later CTE in the same WITH clause?

A. Yes
B. No, only forward references are allowed
C. Only for recursive CTEs
D. Only with WITH FORWARD

Answer: B
B is correct. Later CTEs can reference earlier ones (forward only). This is like forward declarations in programming languages.

MCQ 9

Which is the canonical pattern for a recursive CTE?

A. Anchor UNION recursive
B. Anchor UNION ALL recursive, with termination in recursive WHERE
C. Two anchors
D. Start with just the recursive member

Answer: B
B is correct. Anchor first, UNION ALL, then recursive member with WHERE termination.

MCQ 10

What is silently truncated when column types mismatch between anchor and recursive member?

A. Integer values
B. Strings (sized by the anchor)
C. Dates
D. Nothing — MySQL errors

Answer: B
B is correct. The anchor determines string lengths. If the recursive member generates longer strings, they are truncated without warning. CAST in the anchor to a generous size.

MCQ 11

Which of these is NOT a valid use case for a recursive CTE?

A. Employee manager hierarchy
B. Generating a date range
C. Filling missing months in a report
D. Indexing a frequently queried column

Answer: D
D is correct. Indexing is done with CREATE INDEX, unrelated to CTEs. The other three are classic recursive-CTE patterns.

MCQ 12

What's the practical difference between a CTE and a derived table (subquery in FROM)?

A. None — they are identical
B. CTE can be referenced multiple times and supports recursion
C. CTE is persisted
D. CTE uses a different syntax only

Answer: B
B is correct. Derived tables can only be used once where declared. CTEs can be referenced any number of times in the same statement and enable recursion.

MCQ 13

How does MySQL 8.0 typically execute a non-recursive CTE?

A. It materializes it once and caches the result
B. It often inlines it (evaluating per reference), similar to a derived table
C. It creates a temporary table always
D. It caches in a session-level view

Answer: B
B is correct. MySQL 8.0 often inlines non-recursive CTEs. If you need guaranteed single evaluation of an expensive CTE, use a TEMPORARY TABLE or set the optimizer hint.

MCQ 14

What does WITH RECURSIVE numbers AS (SELECT 1 UNION ALL SELECT n+1 FROM numbers WHERE n < 0) SELECT * FROM numbers; return?

A. 1 through infinity
B. Just 1 (recursive member's WHERE is immediately false)
C. Error
D. Empty set

Answer: B
B is correct. The anchor produces n=1. The recursive member's WHERE (n<0) is false on the very first iteration, so recursion ends. Result: just the anchor's row.

MCQ 15

What is the correct way to traverse a 5-level deep tree if cte_max_recursion_depth is set to 3?

A. Rewrite the query
B. Increase cte_max_recursion_depth to at least 5
C. Use UNION instead of UNION ALL
D. It's impossible

Answer: B
B is correct. Raise the limit: SET @@cte_max_recursion_depth = 100;. Verify termination logic is correct first — raising the limit on a runaway query just delays the error.

MCQ 16

Which statement types can use a CTE?

A. Only SELECT
B. SELECT, INSERT, UPDATE, DELETE
C. Only SELECT and INSERT
D. Only DDL

Answer: B
B is correct. MySQL 8.0 supports CTEs in all four DML statements.

MCQ 17

To make a CTE reusable across multiple separate queries, you should:

A. Add WITH GLOBAL
B. Convert it into a VIEW
C. Put it in a stored procedure
D. Copy-paste it

Answer: B
B is correct. Views are the persistent way to reuse SELECT logic across queries. CTEs are statement-scoped. Stored procedures can also work but are overkill for simple reuse.

MCQ 18

Why can a WITH block NOT be used on its own (without a following SELECT/INSERT/UPDATE/DELETE)?

A. CTEs must be consumed — they define a result only within a larger statement
B. It's a syntax quirk of MySQL
C. They can be, but it's uncommon
D. Because of backward compatibility

Answer: A
A is correct. A CTE is a named subquery. Without an outer statement to use the CTE, it has no purpose. MySQL raises a syntax error.

MCQ 19

In WITH t AS (SELECT 1 AS x) SELECT x FROM t, what is returned?

A. 1
B. x
C. NULL
D. Error

Answer: A
A is correct. The CTE has one row with column x = 1. The outer SELECT returns that value.

MCQ 20

If your recursive CTE might produce cycles (graph with loops), how should you protect against infinite recursion beyond just the depth limit?

A. Use UNION instead of UNION ALL
B. Track the visited path in the CTE and filter out revisits in the WHERE
C. Use a named cursor
D. Rewrite as non-recursive

Recursive CTE for dates, LEFT JOIN + COALESCE.

CREATE TABLE sales (sale_date DATE PRIMARY KEY, amount DECIMAL(10,2));
INSERT INTO sales VALUES
  ('2026-04-10',1200),('2026-04-12',2400),
  ('2026-04-14',1500),('2026-04-16',1100);

WITH RECURSIVE dates AS (
  SELECT (SELECT MIN(sale_date) FROM sales) AS d
  UNION ALL
  SELECT d + INTERVAL 1 DAY
  FROM dates
  WHERE d < (SELECT MAX(sale_date) FROM sales)
)
SELECT d.d AS sale_date,
       COALESCE(s.amount, 0) AS amount
FROM dates d
LEFT JOIN sales s ON s.sale_date = d.d
ORDER BY d.d;

Challenge 8: Rewrite Nested Subquery with CTEs

Hard

Given this 3-level nested subquery, rewrite it using CTEs for readability:

SELECT name, total
FROM (SELECT customer_id, SUM(amount) AS total FROM orders
      WHERE status='paid' GROUP BY customer_id) t
JOIN customers c ON c.id = t.customer_id
WHERE total > (SELECT AVG(total) FROM
    (SELECT customer_id, SUM(amount) AS total FROM orders
     WHERE status='paid' GROUP BY customer_id) t2);

Sample Input

orders and customers tables.

Sample Output

Customers above average paid total.

Define paid totals once as a CTE.

WITH paid_totals AS (
  SELECT customer_id, SUM(amount) AS total
  FROM orders WHERE status = 'paid'
  GROUP BY customer_id
),
threshold AS (
  SELECT AVG(total) AS avg_total FROM paid_totals
)
SELECT c.name, p.total
FROM paid_totals p
JOIN customers c ON c.id = p.customer_id
CROSS JOIN threshold t
WHERE p.total > t.avg_total
ORDER BY p.total DESC;

Need to Review the Concepts?

Go back to the detailed notes for this chapter.

Read Chapter Notes

Want to master SQL and databases with a mentor?

Explore our MySQL Masterclass