Snowflake Common Table Expressions (CTE)

What is a CTE?

A Common Table Expression (CTE) is like a temporary table that you can use during the execution of a single SQL statement. Think of it as a way to organize complex SQL queries. It's defined within a WITH clause at the beginning of a SQL query, and it makes your SQL statement easier to read and maintain.

How does it work?

You start by naming your CTE and optionally specifying the names of its columns. Then, you write a SQL query (usually a SELECT) that fills the CTE with data. This data acts just like a table, which you can use in the rest of your SQL statement.

Syntax example

In this example:

• my_cte is the name of the CTE.
• column_1, column_2 are the names of the columns in the CTE.
• The CTE gets its data from a query that selects column_A and column_B from some_table.

Common use cases

• Simplifying Complex Queries: Break down complicated queries into simpler parts. For example, you can use a CTE to perform complex filtering or calculations, and then use the result to do further queries.
• Recursive Queries: Handle hierarchical or recursive data, like finding all subordinates of a manager in an employee table.

Why use CTEs?

CTEs make your queries modular (easy to piece together) and maintainable. They help you organize your SQL code, making it clearer and easier to understand, especially when dealing with multiple steps of data processing.

💡 Key points to remember

• A CTE is only available during the execution of the query where it’s defined—it’s not stored in the database.
• Avoid using names that are the same as existing tables or SQL functions for your CTEs to prevent confusion.
• CTEs can be non-recursive (the default) or recursive, where they can reference themselves to handle data that's nested or hierarchical.

Examples

Suppose we have a table that logs each character's visit to various planets and the number of times those visits occurred. We'll first calculate the average number of visits per planet across all characters, and then summarize how each character's average compares to the overall average.

Using a single CTE

Referencing a CTE within another CTE

Your AI copilot for Snowflake SQL

Write production-ready SQL with AI-based suggestions.

Get Started