CTE and Recursive CTE in Snowflake

A Common Table Expression (CTE) is a temporary, named result set that you can use within a query. Defined using the WITH clause, a CTE acts like a short-term view that simplifies complex queries. It consists of a name, an optional list of column names, and a query (usually a SELECT statement) that retrieves data. The result behaves like a table, where each column in the output matches the defined column names (if provided).The WITH clause (CTE) and the outer SELECT statement run together as a single SQL execution. The CTE itself does not execute independently,it acts as a temporary result set that the main query uses immediately.

CTE & RCTE

Syntax for CTE:

WITH
    my_cte (cte_col_1, cte_col_2) AS (
        SELECT col_1, col_2
            FROM tablename ##internalquery
    )
SELECT ... FROM my_cte;

SampleExamples:

Step1: Create Temp Table Script

##Create Table

CREATE temporary TABLE employees (
    id INT AUTOINCREMENT PRIMARY KEY,
    name STRING,
    department STRING,
    monthly_salary DECIMAL(10,2)
);

##Insert Sample Data
INSERT INTO employees (name, department, monthly_salary) VALUES
('Alice Johnson', 'IT', 6000),
('Bob Smith', 'HR', 4500),
('Charlie Davis', 'IT', 7000),
('David Brown', 'Finance', 5200),
('Emma Wilson', 'IT', 4800),
('Franklin White', 'IT', 5500);

##QueryData

select…