Snowflake
Common Table Expressions (CTE)

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


_10
WITH my_cte (column_1, column_2) AS (
_10
SELECT column_a, column_b
_10
FROM some_table
_10
)
_10
SELECT * FROM my_cte;

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.

Average number of visits per planet

Input

Table: galactic_visits

character_idcharacter_nameplanetvisit_count
1Arthur DentEarth5
1Arthur DentVogon Ship2
2Ford PrefectBetelgeuse3
2Ford PrefectEarth4
3Zaphod BeeblebroxBetelgeuse5
3Zaphod BeeblebroxMagrathea1

Snowflake SQL Query


_10
WITH planet_average AS (
_10
SELECT planet, AVG(visit_count)::decimal(2,1) AS avg_visits
_10
FROM galactic_visits
_10
GROUP BY planet
_10
)
_10
SELECT planet, avg_visits AS average_visits
_10
FROM planet_average;

Output

planetaverage_visits
Earth4.5
Vogon Ship2.0
Betelgeuse4.0
Magrathea1.0

Average number of visits per planet and character

Input

Table: galactic_visits

character_idcharacter_nameplanetvisit_count
1Arthur DentEarth5
1Arthur DentVogon Ship2
2Ford PrefectBetelgeuse3
2Ford PrefectEarth4
3Zaphod BeeblebroxBetelgeuse5
3Zaphod BeeblebroxMagrathea1

Snowflake SQL Query


_12
WITH planet_average AS (
_12
SELECT planet, AVG(visit_count)::decimal(2,1) AS avg_visits
_12
FROM galactic_visits
_12
GROUP BY planet
_12
),
_12
character_planet_comparison AS (
_12
SELECT g.character_name, g.planet, g.visit_count, p.avg_visits
_12
FROM galactic_visits g
_12
JOIN planet_average p ON g.planet = p.planet
_12
)
_12
SELECT character_name, planet, visit_count AS individual_visits, avg_visits::int AS average_visits
_12
FROM character_planet_comparison;

Output

character_nameplanetindividual_visitsaverage_visits
Arthur DentEarth54.5
Ford PrefectEarth44.5
Arthur DentVogon Ship22.0
Ford PrefectBetelgeuse34.0
Zaphod BeeblebroxBetelgeuse54.0
Zaphod BeeblebroxMagrathea11.0
Y42 SQL completion user interface

Your AI copilot for Snowflake SQL

Write production-ready SQL with AI-based suggestions.

Start for free