π§© 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_id | amount |
|---|---|
| 102 | 800 |
| 103 | 1200 |
How to Read the CTEβ
WITH high_value_orders AS (...)defines a named query result.- The query inside the parentheses selects orders above 500.
FROM high_value_ordersuses that result in the finalSELECT.
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_name | completed_order_count | total_spent | Calculation |
|---|---|---|---|
| Asha | 2 | 1200 | 400 + 800 |
| Daniel | 2 | 1400 | 1200 + 200 |
| Maya | 3 | 900 | 300 + 500 + 100 |
| Ravi | 2 | 1300 | 700 + 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_name | completed_order_count | total_spent |
|---|---|---|
| Daniel | 2 | 1400 |
| Ravi | 2 | 1300 |
| Asha | 2 | 1200 |
Read the query as three steps:
completed_orders: Keep completed orders: 12 rows become 9.customer_totals: Group those orders by customer: 9 rows become 4.- 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_name | completed_order_count | total_spent |
|---|---|---|
| Daniel | 2 | 1400 |
| Ravi | 2 | 1300 |
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_id | emp_name | mgr_id | level |
|---|---|---|---|
| 1 | Anil | NULL | 1 |
| 2 | Bhavna | 1 | 2 |
| 3 | Chirag | 1 | 2 |
| 4 | Divya | 2 | 3 |
| 5 | Eshan | 2 | 3 |
| 6 | Farah | 3 | 3 |
| 7 | Gaurav | 3 | 3 |
π³ 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β
| Benefit | Explanation |
|---|---|
| π§Ή Clean Syntax | Breaks complex queries into readable blocks |
| π Reusability | Used multiple times within a query (especially in recursive CTEs) |
| π§ͺ Easier Debugging | Can run the CTE independently during development |
| π Modular Design | Makes queries more maintainable and scalable |
π Pro Tipsβ
- Recursive CTEs must include an
anchorand arecursivepart. - Always use a
WHEREclause orLEVELlimit to avoid infinite loops. - Snowflake supports recursion up to 1000 levels by default.
- Use
LEVEL,ROW_NUMBER()orRANK()for reporting-friendly outputs.
β Summaryβ
| CTE Type | Use Case |
|---|---|
| Non-recursive | Break down complex queries into steps |
| Recursive | Deal with tree/hierarchy structures |
| Multi-CTE Chain | Build 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β
| Function | Description |
|---|---|
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.