Common Table Expressions (CTEs) in Snowflake - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to start a Common Table Expression (CTE) in Snowflake.

Snowflake

WITH [1] AS (SELECT * FROM employees)

Drag options to blanks, or click blank then click option'

AFROM

Bcte

CSELECT

DWITH

Attempts:

3 left

💡 Hint

Common Mistakes

Using SQL keywords like SELECT or FROM as the CTE name.

Omitting the CTE name after WITH.

The name of the CTE comes right after the WITH keyword. Here, cte is a placeholder for the CTE name.

2fill in blank

medium

Complete the code to select all columns from the CTE named 'sales_data'.

Snowflake

SELECT * FROM [1];

Drag options to blanks, or click blank then click option'

Aemployees

Borders

Csales_data

Dcustomers

Attempts:

3 left

💡 Hint

Common Mistakes

Selecting from a table name instead of the CTE name.

Using incorrect CTE name.

To use the CTE, you select from its name. Here, the CTE is named sales_data.

3fill in blank

hard

Fix the error in the CTE syntax by completing the missing keyword.

Snowflake

WITH sales_summary [1] (SELECT region, SUM(amount) FROM sales GROUP BY region) SELECT * FROM sales_summary;

Drag options to blanks, or click blank then click option'

AAS

BBY

CIS

DON

Attempts:

3 left

💡 Hint

Common Mistakes

Using IS instead of AS.

Omitting the keyword entirely.

The keyword AS is required after the CTE name to define its query.

4fill in blank

hard

Fill both blanks to create a CTE named 'top_customers' that selects customer_id and total_spent.

Snowflake

WITH [1] [2] (SELECT customer_id, SUM(amount) AS total_spent FROM orders GROUP BY customer_id) SELECT * FROM top_customers;

Drag options to blanks, or click blank then click option'

Atop_customers

BAS

CIS

Dcustomers

Attempts:

3 left

💡 Hint

Common Mistakes

Swapping the order of name and keyword.

Using IS instead of AS.

The CTE name is top_customers and the keyword AS defines the CTE query.

5fill in blank

hard

Fill all three blanks to create two CTEs named 'recent_orders' and 'high_value' and select from 'high_value'.

Snowflake

WITH [1] [2] (SELECT * FROM orders WHERE order_date > CURRENT_DATE - INTERVAL '30' DAY), [3] AS (SELECT * FROM [1] WHERE amount > 1000) SELECT * FROM high_value;

Drag options to blanks, or click blank then click option'

Arecent_orders

BAS

Chigh_value

Dorders

Attempts:

3 left

💡 Hint

Common Mistakes

Using wrong CTE names.

Omitting AS keyword.

Not separating CTEs with commas.

The first CTE is named recent_orders, followed by the keyword AS. The second CTE is named high_value.

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