Common Table Expressions (CTEs) in Snowflake - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding CTE Execution Order

Consider the following Snowflake SQL query using CTEs:

WITH first_cte AS (
  SELECT 1 AS num
), second_cte AS (
  SELECT num + 1 AS num FROM first_cte
)
SELECT * FROM second_cte;

What will be the output of this query?

Snowflake

WITH first_cte AS (
  SELECT 1 AS num
), second_cte AS (
  SELECT num + 1 AS num FROM first_cte
)
SELECT * FROM second_cte;

A Syntax error due to missing semicolon

B A single row with num = 1

C An empty result set

D A single row with num = 2

💡 Hint

Think about how the second CTE uses the first CTE's output.

The first CTE returns one row with num = 1. The second CTE adds 1 to that value, resulting in num = 2. The final SELECT returns this single row.

❓ Configuration

intermediate

CTE with Recursive Query Behavior

Which of the following Snowflake CTE definitions correctly implements a recursive query to generate numbers from 1 to 3?

WITH RECURSIVE numbers AS (
  SELECT 1 AS num
  UNION ALL
  SELECT num + 1 FROM numbers WHERE num < 3
)
SELECT * FROM numbers;

Which option matches this behavior?

Snowflake

WITH RECURSIVE numbers AS (
  SELECT 1 AS num
  UNION ALL
  SELECT num + 1 FROM numbers WHERE num < 3
)
SELECT * FROM numbers;

A Generates rows with num = 1, 2, 3

B Syntax error: Snowflake does not support RECURSIVE keyword

C Generates only num = 1

D Infinite loop causing query timeout

💡 Hint

Snowflake supports recursive CTEs. Check the termination condition num < 3.

This is a valid recursive CTE in Snowflake. The anchor member selects 1. The recursive member adds 1 repeatedly until num >= 3, generating rows for 1, 2, 3.

❓ Architecture

advanced

CTE Impact on Query Performance

In Snowflake, which statement best describes how CTEs affect query performance?

A CTEs are always materialized as temporary tables, increasing query time

B CTEs disable query optimization, causing slower execution

C CTEs are inlined into the main query, so they do not always increase query time

D CTEs cause Snowflake to create permanent tables, which slows queries

💡 Hint

Think about how Snowflake processes CTEs internally.

Snowflake typically inlines CTEs into the main query, meaning they are not materialized separately unless forced. This helps avoid unnecessary overhead and can improve performance.

❓ security

advanced

CTE and Data Access Control

Consider a Snowflake environment where a user has SELECT permission on a base table but not on a view built on top of it. If the user runs a query with a CTE referencing the base table, which is true about data access?

A CTEs bypass Snowflake's access control, so user sees all data

B User can access data through the CTE because it references the base table directly

C User cannot access data through the CTE if the base table is restricted

D User must have explicit permission on the CTE to access data

💡 Hint

Consider how permissions apply to base tables versus views and CTEs.

CTEs are just query constructs referencing base tables. If the user has SELECT permission on the base table, they can access data through the CTE. Permissions on views do not affect this.

❓ service_behavior

expert

CTE with Multiple References and Query Result

Given the following Snowflake SQL query:

WITH cte AS (
  SELECT 10 AS val
)
SELECT c1.val AS first_val, c2.val AS second_val
FROM cte c1
JOIN cte c2 ON c1.val = c2.val;

What is the output of this query?

Snowflake

WITH cte AS (
  SELECT 10 AS val
)
SELECT c1.val AS first_val, c2.val AS second_val
FROM cte c1
JOIN cte c2 ON c1.val = c2.val;

A One row with first_val = 10 and second_val = 10

B Two rows with first_val = 10 and second_val = 10

C Empty result set due to join condition

D Syntax error due to aliasing

💡 Hint

Think about how the join works when both sides have one identical row.

Both c1 and c2 refer to the same single-row CTE with val = 10. The join condition matches this row to itself, producing one row with both columns equal to 10.