Skip to main content

🧩 Mastering CTEs (Common Table Expressions) in Snowflake


πŸ“˜ What is a CTE?​

A CTE (Common Table Expression) is a temporary result set that you can reference within a SQL query. It improves code readability, supports modular query design, and enables recursive logic.

Think of a CTE as a named subquery that behaves like a virtual table.


πŸ—οΈ Basic Syntax​

WITH cte_name AS (
SELECT ...
FROM ...
WHERE ...
)
SELECT *
FROM cte_name;

🧠 You can chain multiple CTEs using commas!


Simple Example: Orders Above 500​

We have a small orders table. Our goal is to display only orders where the amount is greater than 500.

Step 1: Create a Small Sample Table​

Run this in your training database and schema. This temporary table lasts only for your current Snowflake session.

CREATE OR REPLACE TEMPORARY TABLE cte_demo_orders (
order_id INT,
amount INT
);

INSERT INTO cte_demo_orders (order_id, amount) VALUES
(101, 200),
(102, 800),
(103, 1200),
(104, 400);

Step 2: Write a Regular SELECT​

SELECT order_id, amount
FROM cte_demo_orders
WHERE amount > 500
ORDER BY order_id;

Step 3: Put the Filter Inside a CTE​

Give the filtered result a name: high_value_orders. Then read from that name in the final query.

WITH high_value_orders AS (
SELECT order_id, amount
FROM cte_demo_orders
WHERE amount > 500
)
SELECT order_id, amount
FROM high_value_orders
ORDER BY order_id;

Output for Both Queries​

order_idamount
102800
1031200

How to Read the CTE​

  1. WITH high_value_orders AS (...) defines a named query result.
  2. The query inside the parentheses selects orders above 500.
  3. FROM high_value_orders uses that result in the final SELECT.

Important: high_value_orders is available only within this SQL statement. It does not create a stored table.

For this simple filter, a regular SELECT is sufficient. The CTE introduces a named step, which becomes useful as a query grows into multiple steps.

Try It Yourself​

Change the CTE filter to amount > 1000. Predict the output, then run the query.

Check your result

Only order 103, with an amount of 1200, should remain.


Next Example: Customer Spending from Completed Orders​

Now that you can name a filtered result, let's build on it with two CTEs. This example uses one table with 12 orders and introduces GROUP BY, COUNT(), and SUM().

Goal: Find customers whose completed orders total at least 1000. Pending and cancelled orders must not count toward their spending.

Step 1: Create More Sample Orders​

Run this in the same training session. We use a separate temporary table so you can still run the first example.

CREATE OR REPLACE TEMPORARY TABLE cte_customer_orders (
order_id INT,
customer_name STRING,
amount INT,
order_status STRING
);

INSERT INTO cte_customer_orders
(order_id, customer_name, amount, order_status)
VALUES
(201, 'Asha', 400, 'COMPLETED'),
(202, 'Ravi', 700, 'COMPLETED'),
(203, 'Maya', 300, 'COMPLETED'),
(204, 'Asha', 800, 'COMPLETED'),
(205, 'Ravi', 900, 'CANCELLED'),
(206, 'Maya', 500, 'COMPLETED'),
(207, 'Daniel', 1200, 'COMPLETED'),
(208, 'Asha', 600, 'PENDING'),
(209, 'Ravi', 600, 'COMPLETED'),
(210, 'Maya', 100, 'COMPLETED'),
(211, 'Daniel', 200, 'COMPLETED'),
(212, 'Daniel', 500, 'CANCELLED');

Step 2: Filter Completed Orders with One CTE​

Start with the filtering pattern from the simple example:

WITH completed_orders AS (
SELECT order_id, customer_name, amount
FROM cte_customer_orders
WHERE order_status = 'COMPLETED'
)
SELECT order_id, customer_name, amount
FROM completed_orders
ORDER BY order_id;

This returns 9 orders. Orders 205, 208, and 212 are excluded because they are cancelled or pending.

Step 3: Add a Second CTE to Calculate Customer Totals​

The second CTE reads from completed_orders, rather than from the original table. This means it only counts and sums completed orders.

WITH completed_orders AS (
SELECT order_id, customer_name, amount
FROM cte_customer_orders
WHERE order_status = 'COMPLETED'
),
customer_totals AS (
SELECT
customer_name,
COUNT(*) AS completed_order_count,
SUM(amount) AS total_spent
FROM completed_orders
GROUP BY customer_name
)
SELECT customer_name, completed_order_count, total_spent
FROM customer_totals
WHERE total_spent >= 1000
ORDER BY total_spent DESC, customer_name;

Syntax to notice: Use WITH once and separate the CTE definitions with a comma. The second CTE can reference the first one.

Understand the Intermediate Totals​

Before the final spending filter, customer_totals contains these four rows:

customer_namecompleted_order_counttotal_spentCalculation
Asha21200400 + 800
Daniel214001200 + 200
Maya3900300 + 500 + 100
Ravi21300700 + 600

The calculation column above is an explanation, not a column produced by the SQL.

Final Output​

The final query keeps customers with total_spent >= 1000, so Maya is excluded:

customer_namecompleted_order_counttotal_spent
Daniel21400
Ravi21300
Asha21200

Read the query as three steps:

  1. completed_orders: Keep completed orders: 12 rows become 9.
  2. customer_totals: Group those orders by customer: 9 rows become 4.
  3. Final SELECT: Keep customers who spent at least 1000: 4 rows become 3.

Each step has a name and a clear purpose. Both CTE names are available only within this SQL statement.

Try It Yourself​

Change the final filter to total_spent >= 1300. Predict which customers remain before running it.

Check your result
customer_namecompleted_order_counttotal_spent
Daniel21400
Ravi21300

Ravi is included because >= 1300 includes a total of exactly 1300.


βœ… 2. Sample Table: employees​

CREATE OR REPLACE TABLE employees (
emp_id INT,
emp_name STRING,
mgr_id INT
);

INSERT INTO employees VALUES
(1, 'Anil', NULL),
(2, 'Bhavna', 1),
(3, 'Chirag', 1),
(4, 'Divya', 2),
(5, 'Eshan', 2),
(6, 'Farah', 3),
(7, 'Gaurav', 3);

πŸ” Recursive CTE – Organizational Hierarchy​

Recursive CTEs are ideal for hierarchical or tree-based data, such as reporting structures or category trees.

🧠 Objective: Get full employee β†’ manager hierarchy​


πŸ“œ Query​

WITH RECURSIVE emp_hierarchy AS (
-- Anchor: Top-level employee(s)
SELECT emp_id, emp_name, mgr_id, 1 AS level
FROM employees
WHERE mgr_id IS NULL

UNION ALL

-- Recursive: Join employee to manager
SELECT e.emp_id, e.emp_name, e.mgr_id, h.level + 1
FROM employees e
JOIN emp_hierarchy h ON e.mgr_id = h.emp_id
)

SELECT * FROM emp_hierarchy
ORDER BY level, emp_id;

πŸ“Š Output​

emp_idemp_namemgr_idlevel
1AnilNULL1
2Bhavna12
3Chirag12
4Divya23
5Eshan23
6Farah33
7Gaurav33

🌳 Hierarchical View​

Anil (Level 1)
β”œβ”€β”€ Bhavna (Level 2)
β”‚ β”œβ”€β”€ Divya (Level 3)
β”‚ └── Eshan (Level 3)
└── Chirag (Level 2)
β”œβ”€β”€ Farah (Level 3)
└── Gaurav (Level 3)

πŸ”Ž Benefits for Data Engineers​

BenefitExplanation
🧹 Clean SyntaxBreaks complex queries into readable blocks
πŸ” ReusabilityUsed multiple times within a query (especially in recursive CTEs)
πŸ§ͺ Easier DebuggingCan run the CTE independently during development
πŸ— Modular DesignMakes queries more maintainable and scalable

πŸ” Pro Tips​

  • Recursive CTEs must include an anchor and a recursive part.
  • Always use a WHERE clause or LEVEL limit to avoid infinite loops.
  • Snowflake supports recursion up to 1000 levels by default.
  • Use LEVEL, ROW_NUMBER() or RANK() for reporting-friendly outputs.

βœ… Summary​

CTE TypeUse Case
Non-recursiveBreak down complex queries into steps
RecursiveDeal with tree/hierarchy structures
Multi-CTE ChainBuild layered queries with multiple steps

πŸͺŸ Introduction to Window Functions​

Window functions perform calculations across a set of rows related to the current rowβ€”called a "window" of rowsβ€”without collapsing the result set.

Unlike GROUP BY, window functions retain individual rows and just add extra computed values.


πŸ”Ÿ Most Common Window Functions in SQL​

FunctionDescription
ROW_NUMBER()Assigns a unique sequential number to rows within a partition.
RANK()Gives ranks with gaps for ties.
DENSE_RANK()Ranks like RANK(), but without gaps in ranking.
NTILE(n)Divides rows into n approximately equal groups and assigns a group number.
LAG()Accesses the value from a previous row in the window.
LEAD()Accesses the value from a following row in the window.
FIRST_VALUE()Returns the first value in the window frame.
LAST_VALUE()Returns the last value in the window frame.
SUM()Calculates the running total within a partition.
AVG()Computes the average value within the partition/window.

πŸ§ͺ Sample Table: sales​

CREATE OR REPLACE TABLE sales (
emp_id INT,
emp_name STRING,
dept STRING,
sales_amount INT
);

INSERT INTO sales VALUES
(1, 'Amit', 'Electronics', 1000),
(2, 'Priya', 'Electronics', 1200),
(3, 'Raj', 'Electronics', 800),
(4, 'Anjali', 'Furniture', 700),
(5, 'Karan', 'Furniture', 1500),
(6, 'Divya', 'Furniture', 1100);

1️⃣ ROW_NUMBER()​

SELECT emp_name, dept, sales_amount,
ROW_NUMBER() OVER (PARTITION BY dept ORDER BY sales_amount DESC) AS row_num
FROM sales;

πŸ“˜ Assigns a unique row number for each employee within their department based on sales.


2️⃣ RANK()​

SELECT emp_name, dept, sales_amount,
RANK() OVER (PARTITION BY dept ORDER BY sales_amount DESC) AS rank
FROM sales;

πŸ“˜ Ranks employees in each department by sales. Ties share the same rank with a gap.


3️⃣ DENSE_RANK()​

SELECT emp_name, dept, sales_amount,
DENSE_RANK() OVER (PARTITION BY dept ORDER BY sales_amount DESC) AS dense_rank
FROM sales;

πŸ“˜ Like RANK() but does not skip the next rank in case of ties.


4️⃣ NTILE(2)​

SELECT emp_name, dept, sales_amount,
NTILE(2) OVER (PARTITION BY dept ORDER BY sales_amount DESC) AS ntile_group
FROM sales;

πŸ“˜ Divides rows in each department into 2 groups.


5️⃣ LAG()​

SELECT emp_name, dept, sales_amount,
LAG(sales_amount) OVER (PARTITION BY dept ORDER BY sales_amount) AS prev_sales
FROM sales;

πŸ“˜ Shows the sales amount of the previous employee (within the same department).


6️⃣ LEAD()​

SELECT emp_name, dept, sales_amount,
LEAD(sales_amount) OVER (PARTITION BY dept ORDER BY sales_amount) AS next_sales
FROM sales;

πŸ“˜ Shows the sales amount of the next employee (within the same department).


7️⃣ FIRST_VALUE()​

SELECT emp_name, dept, sales_amount,
FIRST_VALUE(sales_amount) OVER (PARTITION BY dept ORDER BY sales_amount) AS first_sale
FROM sales;

πŸ“˜ Returns the lowest sales value in each department.


8️⃣ LAST_VALUE()​

SELECT emp_name, dept, sales_amount,
LAST_VALUE(sales_amount) OVER (PARTITION BY dept ORDER BY sales_amount
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_sale
FROM sales;

πŸ“˜ Returns the highest sales value in each department.


9️⃣ SUM() (Running Total)​

SELECT emp_name, dept, sales_amount,
SUM(sales_amount) OVER (PARTITION BY dept ORDER BY emp_name) AS running_total
FROM sales;

πŸ“˜ Computes a cumulative total of sales per department.


πŸ”Ÿ AVG() (Running Average)​

SELECT emp_name, dept, sales_amount,
AVG(sales_amount) OVER (PARTITION BY dept ORDER BY emp_name) AS running_avg
FROM sales;

πŸ“˜ Calculates a moving average of sales in each department.