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.

Practice

1. What is the main purpose of using a WITH clause in Snowflake SQL?

easy

A. To set session variables

B. To permanently store data in a new table

C. To create a user-defined function

D. To define a temporary named result set for use in a query

Solution

1. Step 1: Understand the role of the WITH clause

   The WITH clause defines a Common Table Expression (CTE), which is a temporary named result set used within a query.

2. Step 2: Differentiate from other SQL features

   Unlike permanent tables or functions, CTEs exist only during query execution and help organize complex queries.

3. Final Answer:

   To define a temporary named result set for use in a query -> Option D

4. Quick Check:

   WITH clause = temporary named result set [OK]

Hint: WITH means temporary named query part [OK]

Common Mistakes:

• Thinking WITH creates permanent tables
• Confusing WITH with functions or variables
• Assuming WITH changes session settings

2. Which of the following is the correct syntax to start a CTE in Snowflake?

easy

A. CREATE CTE cte_name AS (SELECT * FROM table);

B. DEFINE cte_name SELECT * FROM table;

C. WITH cte_name AS (SELECT * FROM table)

D. BEGIN CTE cte_name SELECT * FROM table;

Solution

1. Step 1: Recall the standard CTE syntax

   In Snowflake, a CTE starts with the keyword WITH followed by the CTE name and AS, then the query in parentheses.

2. Step 2: Compare options to syntax

   WITH cte_name AS (SELECT * FROM table) matches this pattern exactly: WITH cte_name AS (SELECT * FROM table)

3. Final Answer:

   WITH cte_name AS (SELECT * FROM table) -> Option C

4. Quick Check:

   CTE syntax starts with WITH [OK]

Hint: CTE always starts with WITH keyword [OK]

Common Mistakes:

• Using CREATE instead of WITH
• Omitting AS keyword
• Using BEGIN or DEFINE which are invalid here

3. Given the query:

WITH cte AS (SELECT 1 AS num UNION ALL SELECT 2) SELECT SUM(num) FROM cte;

What is the output of this query?

medium

A. 3

B. 2

C. 1

D. Error

Solution

1. Step 1: Understand the CTE content

   The CTE named 'cte' selects two rows with values 1 and 2 under the column 'num'.

2. Step 2: Calculate the SUM of 'num'

   The main query sums the values 1 and 2, resulting in 3.

3. Final Answer:

   3 -> Option A

4. Quick Check:

   1 + 2 = 3 [OK]

Hint: Sum values inside CTE then add [OK]

Common Mistakes:

• Thinking UNION ALL removes duplicates
• Confusing SUM with COUNT
• Expecting syntax error due to CTE

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

WITH cte AS SELECT * FROM employees SELECT * FROM cte;

medium

A. Missing parentheses around the CTE query

B. CTE name cannot be 'cte'

C. CTE cannot be used in the same query

D. Missing AS keyword before SELECT in CTE definition

Solution

1. Step 1: Review CTE syntax requirements

   A CTE query must be enclosed in parentheses after the AS keyword.

2. Step 2: Check the given query

   The query lacks parentheses around the SELECT statement in the CTE definition.

3. Final Answer:

   Missing parentheses around the CTE query -> Option A

4. Quick Check:

   CTE query must be in parentheses [OK]

Hint: CTE query always inside parentheses [OK]

Common Mistakes:

• Omitting parentheses around CTE query
• Thinking CTE names are restricted
• Believing CTEs can't be reused in same query

5. You want to calculate the average salary per department using a CTE named dept_salaries that selects employee salaries and departments. Which query correctly uses the CTE to get the average salary per department?

hard

A. WITH dept_salaries AS SELECT department, salary FROM employees SELECT department, AVG(salary) FROM dept_salaries GROUP BY department;

B. WITH dept_salaries AS (SELECT department, salary FROM employees) SELECT department, AVG(salary) FROM dept_salaries GROUP BY department;

C. WITH dept_salaries (department, salary) AS (SELECT department, salary FROM employees) SELECT AVG(salary) FROM dept_salaries;

D. WITH dept_salaries AS (SELECT department, salary FROM employees) SELECT AVG(salary) FROM employees GROUP BY department;

Solution

1. Step 1: Verify correct CTE syntax and usage

   WITH dept_salaries AS (SELECT department, salary FROM employees) SELECT department, AVG(salary) FROM dept_salaries GROUP BY department; correctly defines the CTE with parentheses and AS, then uses it to select department and average salary grouped by department.

2. Step 2: Check aggregation and grouping

   WITH dept_salaries AS (SELECT department, salary FROM employees) SELECT department, AVG(salary) FROM dept_salaries GROUP BY department; groups by department and calculates AVG(salary) from the CTE, which is the intended calculation.

3. Final Answer:

   WITH dept_salaries AS (SELECT department, salary FROM employees) SELECT department, AVG(salary) FROM dept_salaries GROUP BY department; -> Option B

4. Quick Check:

   CTE with correct syntax and grouping = WITH dept_salaries AS (SELECT department, salary FROM employees) SELECT department, AVG(salary) FROM dept_salaries GROUP BY department; [OK]

Hint: Use WITH ... AS (...) then GROUP BY correctly [OK]

Common Mistakes:

• Omitting parentheses in CTE definition
• Not grouping by department when aggregating
• Using employees table instead of CTE in main query