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.