Common Table Expressions (CTEs) in Snowflake - Commands & Configuration

Introduction

Sometimes you need to break down a complex query into smaller parts to make it easier to read and manage. Common Table Expressions, or CTEs, let you do this by creating temporary named result sets that you can use within your main query.

When you want to organize a long query into smaller, understandable pieces.

When you need to reuse the same subquery multiple times in a larger query.

When you want to improve the readability of your SQL code for yourself or others.

When you want to perform step-by-step data transformations before the final output.

When you want to avoid repeating complex joins or calculations in your query.

Commands

This command creates a CTE named recent_orders that selects orders after January 1, 2024. Then it queries this CTE to count how many recent orders each customer made, sorting customers by their order count.

Terminal

WITH recent_orders AS (
  SELECT order_id, customer_id, order_date
  FROM orders
  WHERE order_date > '2024-01-01'
)
SELECT customer_id, COUNT(order_id) AS order_count
FROM recent_orders
GROUP BY customer_id
ORDER BY order_count DESC;

Expected Output

CUSTOMER_ID | ORDER_COUNT

------------|------------

123         | 5

456         | 3

789         | 2

This command shows how to use multiple CTEs. The first CTE filters recent orders, and the second counts orders per customer. The final query selects customers with more than two orders.

Terminal

WITH recent_orders AS (
  SELECT order_id, customer_id, order_date
  FROM orders
  WHERE order_date > '2024-01-01'
),
customer_totals AS (
  SELECT customer_id, COUNT(order_id) AS total_orders
  FROM recent_orders
  GROUP BY customer_id
)
SELECT * FROM customer_totals WHERE total_orders > 2;

Expected Output

CUSTOMER_ID | TOTAL_ORDERS

------------|-------------

123         | 5

456         | 3

Key Concept

If you remember nothing else from this pattern, remember: CTEs let you name and reuse parts of your query to make complex SQL easier to read and maintain.

Common Mistakes

Not placing the WITH clause at the very start of the query.

Snowflake requires the CTE to be defined before the main SELECT statement; otherwise, it causes a syntax error.

Always start your query with the WITH clause when using CTEs.

Trying to reference a CTE outside the query where it is defined.

CTEs exist only for the duration of the query they are part of and cannot be reused elsewhere.

Use the CTE only within the same query or define a view if reuse is needed across queries.

Using semicolons before the WITH clause in multi-statement scripts.

A semicolon before WITH can end the previous statement and cause the CTE to be treated as a new statement, leading to errors.

Do not put a semicolon before the WITH clause; place it only at the end of the entire query.

Summary

Use WITH to define temporary named result sets called CTEs at the start of your query.

CTEs help break complex queries into smaller, readable parts and can be chained together.

Always write the WITH clause before the main SELECT and use CTEs only within the same query.