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.

Practice

1. What is the main purpose of using a WITH clause (Common Table Expression) in a MySQL query?

easy

A. To permanently store data in a new table

B. To create a temporary named result set that can be referenced within the main query

C. To delete rows from a table

D. To create an index on a table column

Solution

1. Step 1: Understand the role of WITH clause

   The WITH clause defines a temporary named result set, called a Common Table Expression (CTE), which exists only during the execution of the query.

2. Step 2: Differentiate from permanent operations

   Unlike creating tables or indexes, CTEs do not store data permanently but help organize complex queries by breaking them into parts.

3. Final Answer:

   To create a temporary named result set that can be referenced within the main query -> Option B

4. Quick Check:

   WITH clause = temporary named result set [OK]

Hint: WITH creates temporary named results for easier queries [OK]

Common Mistakes:

• Thinking WITH creates permanent tables
• Confusing WITH with DELETE or UPDATE commands
• Assuming WITH creates indexes

2. Which of the following is the correct syntax to start a Common Table Expression in MySQL?

easy

A. DEFINE cte_name AS SELECT * FROM table_name

B. CREATE CTE cte_name SELECT * FROM table_name

C. START CTE cte_name (SELECT * FROM table_name)

D. WITH cte_name AS (SELECT * FROM table_name)

Solution

1. Step 1: Recall the standard syntax for CTE

   The correct syntax to define a CTE starts with the keyword WITH, followed by the CTE name, AS, and then the query in parentheses.

2. Step 2: Compare options with syntax

   Only WITH cte_name AS (SELECT * FROM table_name) matches the correct syntax: WITH cte_name AS (SELECT * FROM table_name). Other options use invalid keywords or structure.

3. Final Answer:

   WITH cte_name AS (SELECT * FROM table_name) -> Option D

4. Quick Check:

   WITH + name + AS + (query) = correct syntax [OK]

Hint: WITH cte_name AS (query) is the standard start [OK]

Common Mistakes:

• Using CREATE or DEFINE instead of WITH
• Omitting AS keyword
• Not enclosing query in parentheses

3. Given the following query, what will be the output?

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

medium

A. All products with total quantity sold greater than 100

B. All products with total quantity sold less than or equal to 100

C. Syntax error due to missing GROUP BY

D. Empty result because CTE is not used properly

Solution

1. Step 1: Analyze the CTE definition

   The CTE named SalesCTE calculates total quantity sold per product by grouping sales by product_id and summing quantity.

2. Step 2: Understand the main query filter

   The main query selects rows from SalesCTE where total_qty is greater than 100, so only products with sales above 100 units appear.

3. Final Answer:

   All products with total quantity sold greater than 100 -> Option A

4. Quick Check:

   CTE sums and filters total_qty > 100 [OK]

Hint: CTE calculates sums; main query filters by condition [OK]

Common Mistakes:

• Confusing > 100 with <= 100
• Thinking GROUP BY is missing (it is present)
• Assuming CTE is unused or causes error

4. Identify the error in the following MySQL query using a CTE:

WITH cte AS (
  SELECT id, name FROM users
)
SELECT * FROM cte WHERE age > 30;

medium

A. CTE query missing a semicolon after closing parenthesis

B. WITH clause must be followed by INSERT statement

C. Column 'age' does not exist in the CTE result

D. CTE name cannot be 'cte'

Solution

1. Step 1: Check columns selected in CTE

   The CTE selects only 'id' and 'name' columns from users table; 'age' is not included.

2. Step 2: Analyze main query filter

   The main query tries to filter on 'age', which is not part of the CTE result, causing an error.

3. Final Answer:

   Column 'age' does not exist in the CTE result -> Option C

4. Quick Check:

   Filter column must be in CTE output [OK]

Hint: Filter columns must be selected in CTE first [OK]

Common Mistakes:

• Assuming semicolon needed inside CTE
• Thinking CTE name 'cte' is invalid
• Believing WITH must precede INSERT

5. You want to find the top 3 customers by total purchase amount using a CTE. Which query correctly uses a CTE to achieve this?

hard

A. WITH CustomerTotals AS (
SELECT customer_id, SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id
)
SELECT customer_id, total_amount
FROM CustomerTotals
ORDER BY total_amount DESC
LIMIT 3;

B. WITH CustomerTotals AS (
SELECT customer_id, amount
FROM orders
)
SELECT customer_id, SUM(amount) AS total_amount
FROM CustomerTotals
GROUP BY customer_id
ORDER BY total_amount DESC
LIMIT 3;

C. WITH CustomerTotals AS (
SELECT customer_id, SUM(amount) AS total_amount
FROM orders
)
SELECT * FROM CustomerTotals
LIMIT 3;

D. WITH CustomerTotals AS (
SELECT customer_id, SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id
ORDER BY total_amount DESC
LIMIT 3
)
SELECT * FROM CustomerTotals;

Solution

1. Step 1: Check CTE calculation and grouping

   WITH CustomerTotals AS ( SELECT customer_id, SUM(amount) AS total_amount FROM orders GROUP BY customer_id ) SELECT customer_id, total_amount FROM CustomerTotals ORDER BY total_amount DESC LIMIT 3; correctly groups orders by customer_id and sums amount inside the CTE.

2. Step 2: Verify ordering and limiting in main query

   WITH CustomerTotals AS ( SELECT customer_id, SUM(amount) AS total_amount FROM orders GROUP BY customer_id ) SELECT customer_id, total_amount FROM CustomerTotals ORDER BY total_amount DESC LIMIT 3; orders by total_amount descending and limits to top 3 customers outside the CTE, which is valid in MySQL.

3. Step 3: Analyze other options for errors

   WITH CustomerTotals AS ( SELECT customer_id, amount FROM orders ) SELECT customer_id, SUM(amount) AS total_amount FROM CustomerTotals GROUP BY customer_id ORDER BY total_amount DESC LIMIT 3; does not sum inside CTE, causing incorrect grouping. WITH CustomerTotals AS ( SELECT customer_id, SUM(amount) AS total_amount FROM orders ) SELECT * FROM CustomerTotals LIMIT 3; misses GROUP BY and ordering. WITH CustomerTotals AS ( SELECT customer_id, SUM(amount) AS total_amount FROM orders GROUP BY customer_id ORDER BY total_amount DESC LIMIT 3 ) SELECT * FROM CustomerTotals; tries to use ORDER BY and LIMIT inside CTE, which is invalid in MySQL.

4. Final Answer:

   Option A query correctly finds top 3 customers by total purchase amount -> Option A

5. Quick Check:

   Group and sum in CTE, order and limit outside [OK]

Hint: Group and sum in CTE; order and limit outside [OK]

Common Mistakes:

• Placing ORDER BY and LIMIT inside CTE
• Not grouping before summing
• Trying to sum outside CTE without grouping