Common Table Expressions (WITH) in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of a simple CTE query

What is the output of this query?

WITH SalesCTE AS (SELECT product_id, SUM(quantity) AS total_qty FROM sales GROUP BY product_id) SELECT * FROM SalesCTE WHERE total_qty > 100;

MySQL

WITH SalesCTE AS (SELECT product_id, SUM(quantity) AS total_qty FROM sales GROUP BY product_id) SELECT * FROM SalesCTE WHERE total_qty > 100;

A Rows with product_id and total_qty where total_qty is greater than 100

B All rows from sales table without aggregation

C Syntax error due to missing alias

D Empty result set because no product has quantity over 100

💡 Hint

Think about what the CTE does before the main SELECT.

The CTE calculates total quantity per product. The main query filters products with total quantity over 100, so it returns those rows.

📝 Syntax

intermediate

Identify the syntax error in this CTE

Which option correctly identifies the syntax error in this query?

WITH cte AS SELECT id, name FROM users SELECT * FROM cte;

MySQL

WITH cte AS SELECT id, name FROM users SELECT * FROM cte;

A Incorrect alias name for CTE

B Missing comma between CTE and main query

C Missing parentheses around the CTE query

D No error, query is valid

💡 Hint

Check the syntax for defining a CTE in MySQL.

In MySQL, the CTE query must be enclosed in parentheses after the AS keyword.

❓ optimization

advanced

Optimizing repeated subqueries with CTE

Given this query:

SELECT u.id, (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS order_count FROM users u WHERE (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) > 5;

Which rewritten query using a CTE is more efficient?

MySQL

SELECT u.id, (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS order_count FROM users u WHERE (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) > 5;

A WITH OrderCounts AS (SELECT user_id, COUNT(*) AS cnt FROM orders GROUP BY user_id) SELECT u.id, oc.cnt FROM users u JOIN OrderCounts oc ON u.id = oc.user_id WHERE oc.cnt > 5;

B WITH OrderCounts AS (SELECT user_id, COUNT(*) AS cnt FROM orders) SELECT u.id, oc.cnt FROM users u JOIN OrderCounts oc ON u.id = oc.user_id WHERE oc.cnt > 5;

C WITH OrderCounts AS (SELECT user_id, COUNT(*) AS cnt FROM orders GROUP BY user_id) SELECT u.id, oc.cnt FROM users u LEFT JOIN OrderCounts oc ON u.id = oc.user_id WHERE oc.cnt > 5;

D WITH OrderCounts AS (SELECT user_id, COUNT(*) AS cnt FROM orders GROUP BY user_id) SELECT u.id FROM users u WHERE u.id IN (SELECT user_id FROM OrderCounts WHERE cnt > 5);

💡 Hint

Avoid repeating the same subquery multiple times by calculating once in a CTE.

Option A calculates order counts once in the CTE and joins to filter users efficiently. Option A misses GROUP BY causing error. Option A uses LEFT JOIN but filters on oc.cnt > 5 which excludes NULLs, similar to INNER JOIN but less clear. Option A returns only user ids, missing order_count column.

🔧 Debug

advanced

Debugging recursive CTE for factorial calculation

This recursive CTE is intended to calculate factorial of 5:

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

What is the output?

MySQL

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

A 24

B 120

C Syntax error due to missing RECURSIVE keyword

D Empty result set

💡 Hint

Trace the recursive steps from n=1 to n=5.

The CTE starts at n=1 with factorial 1, then recursively multiplies up to n=5, resulting in 120.

🧠 Conceptual

expert

Understanding CTE scope and visibility

Consider these two queries:

1. WITH cte AS (SELECT 1 AS val) SELECT * FROM cte;
2. SELECT * FROM cte;

Why does the second query fail?

A The second query is missing a FROM clause

B CTEs require explicit global declaration to be reused

C CTEs are only supported in SELECT statements with JOINs

D CTEs exist only within the query they are defined and are not visible outside

💡 Hint

Think about the lifetime of a CTE in SQL execution.

CTEs are temporary named result sets valid only for the single query they are defined in. They cannot be referenced in separate queries.