CTE referencing another CTE in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of nested CTE query

Given the following SQL query with two CTEs where the second references the first, what is the output?

SQL

WITH FirstCTE AS (
  SELECT 1 AS num UNION ALL SELECT 2 UNION ALL SELECT 3
),
SecondCTE AS (
  SELECT num, num * 10 AS multiplied FROM FirstCTE
)
SELECT * FROM SecondCTE ORDER BY num;

A [{"num":1,"multiplied":1},{"num":2,"multiplied":2},{"num":3,"multiplied":3}]

B [{"num":1,"multiplied":10},{"num":2,"multiplied":20},{"num":3,"multiplied":30}]

C SyntaxError

D []

💡 Hint

Look at how the second CTE uses the first CTE's output and multiplies the numbers by 10.

The first CTE produces numbers 1, 2, and 3. The second CTE selects these numbers and multiplies each by 10. The final SELECT returns these results ordered by num.

📝 Syntax

intermediate

Identify the syntax error in CTE referencing

Which option contains a syntax error when defining a CTE that references another CTE?

SQL

WITH A AS (SELECT 1 AS val),
B AS (SELECT val + 1 FROM A)
SELECT * FROM B;

A WITH A AS (SELECT 1 AS val), B AS (SELECT val + 1 FROM A); SELECT * FROM B;

B WITH A AS (SELECT 1 AS val), B AS (SELECT val + 1 FROM A) SELECT * FROM B;

C ;B MORF * TCELES )A MORF 1 + lav TCELES( SA B ,)lav SA 1 TCELES( SA A HTIW

D WITH A AS (SELECT 1 AS val) B AS (SELECT val + 1 FROM A) SELECT * FROM B;

💡 Hint

Check the commas separating CTE definitions.

Option D misses the comma between the two CTE definitions, causing a syntax error.

❓ optimization

advanced

Optimizing multiple CTE references

Consider two CTEs where the second references the first multiple times. Which option best optimizes the query to avoid repeated computation?

SQL

WITH BaseCTE AS (
  SELECT id, value FROM data_table WHERE value > 10
),
AggCTE AS (
  SELECT id, COUNT(*) AS cnt FROM BaseCTE GROUP BY id
)
SELECT * FROM AggCTE;

A Use a single CTE and repeat the filtering logic inside the second CTE instead of referencing the first.

B Reference BaseCTE multiple times in AggCTE to ensure fresh data each time.

C Reference BaseCTE once in AggCTE to reuse the filtered data and avoid recalculations.

D Avoid using CTEs and write the entire query as a nested subquery.

💡 Hint

Reusing filtered data avoids repeating expensive operations.

Referencing the first CTE once in the second CTE allows reuse of the filtered data, improving performance by avoiding repeated filtering.

🔧 Debug

advanced

Debugging incorrect CTE reference

Given this SQL snippet, what error will it raise? WITH First AS (SELECT 1 AS x), Second AS (SELECT y FROM First) SELECT * FROM Second;

A Column 'y' does not exist error

B Syntax error near 'Second'

C No error, returns one row with value 1

D Table 'First' does not exist error

💡 Hint

Check the column names used in the second CTE.

The first CTE defines column 'x', but the second CTE tries to select 'y' which does not exist, causing a column not found error.

🧠 Conceptual

expert

Understanding CTE evaluation order

Which statement best describes how CTEs referencing other CTEs are evaluated in SQL?

A CTEs are evaluated in the order they are defined, with later CTEs able to reference earlier ones.

B Each CTE is evaluated only when referenced, starting from the last defined CTE backwards.

C All CTEs are evaluated simultaneously before the main query runs.

D CTEs are materialized and stored permanently in the database for reuse.

💡 Hint

Think about how SQL processes WITH clauses step-by-step.

CTEs are evaluated in the order they are defined. Later CTEs can reference earlier ones, but not vice versa.