Snowflake Subqueries and CTEs

Complex questions break into smaller questions. Subqueries and common table expressions, called CTEs, let SQL follow the same path. Each small step gets a name, and the final query reads like a clear recipe rather than a tangled knot.

The Recipe Card Comparison

A cooking recipe lists steps: chop vegetables, make the sauce, cook the pasta, combine everything. Nobody tries to do every task in a single move. A CTE works like a numbered step on that card. Each step produces something small, and the last step combines the results.

STEP BY STEP WITH CTEs
======================

 WITH monthly_sales AS (...)      step 1: total per month
 ,    best_months    AS (...)     step 2: keep months above average
 SELECT ... FROM best_months;     final: show the answer

 monthly_sales  --->  best_months  --->  final result

Subqueries

A subquery is a query inside another query. It can appear in the WHERE clause, the FROM clause, or the SELECT list.

-- Scalar subquery: returns one value
SELECT order_id, amount
FROM orders
WHERE amount > (SELECT AVG(amount) FROM orders);

-- IN subquery: returns a list
SELECT name
FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders WHERE amount > 500);

-- EXISTS subquery: checks for any match
SELECT c.name
FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);

-- Correlated subquery: refers to the outer row
SELECT o.order_id, o.amount
FROM orders o
WHERE o.amount > (SELECT AVG(amount) FROM orders i
                  WHERE i.customer_id = o.customer_id);

The correlated version recalculates the inner average for every customer, which answers the question "which orders beat that customer's own average?"

Common Table Expressions

The WITH keyword defines named results before the main query. The same query written with CTEs reads from top to bottom.

WITH monthly_sales AS (
  SELECT DATE_TRUNC('month', order_date) AS month,
         SUM(amount)                     AS revenue
  FROM orders
  GROUP BY 1
),
average_month AS (
  SELECT AVG(revenue) AS avg_revenue FROM monthly_sales
)
SELECT m.month, m.revenue
FROM monthly_sales m
CROSS JOIN average_month a
WHERE m.revenue > a.avg_revenue
ORDER BY m.month;

The first CTE feeds the second, and the main query uses both. Each block can run alone in a worksheet for easy testing.

Subquery or CTE?

SITUATION                               BEST CHOICE
--------------------------------------  -------------
One quick filter value                  Subquery
Same result used twice in one query     CTE
Several steps that build on each other  CTE
Existence check                         EXISTS subquery
Walking a hierarchy                     Recursive CTE

Recursive CTEs for Hierarchies

Organization charts, folder trees, and parts lists have unknown depth. A recursive CTE starts with a base row set and repeatedly adds one more level until nothing new appears.

WITH RECURSIVE org AS (
  -- anchor: the top manager
  SELECT emp_id, name, manager_id, 1 AS level
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  -- repeat: everyone who reports to someone already found
  SELECT e.emp_id, e.name, e.manager_id, o.level + 1
  FROM employees e
  JOIN org o ON e.manager_id = o.emp_id
)
SELECT level, name FROM org ORDER BY level, name;

RECURSION WALK
==============
Level 1:  Meena (CEO)
Level 2:  Arun, Divya            report to Meena
Level 3:  Kabir, Sana, Tarun     report to Arun or Divya

Snowflake supports the CONNECT BY clause for the same purpose. Both styles suit trees and graphs.

Performance Habits

• Snowflake optimizes CTEs like subqueries, so naming a step costs nothing extra.
• Filter early inside each CTE to shrink the data before joins.
• Avoid correlated subqueries on huge tables when a join gives the same answer.
• Select only needed columns inside every step.

A Practical Scenario

A marketing analyst wants customers whose latest purchase exceeded their personal average. The analyst builds one CTE for per-customer averages, a second CTE for each customer's latest order, and a final join that compares the two. Each block gets tested alone. A colleague reads the query a month later and understands it in a minute.

Frequently Asked Questions

Does a CTE store its result?

A CTE is a name for a query, and Snowflake decides at run time how to execute it. Define a temporary table when a result needs to persist across several statements.

When does a correlated subquery slow a query?

A correlated subquery may repeat work for every outer row. Rewrite it as a join with an aggregate when the outer table holds millions of rows, and compare the two versions in Query Profile.

Can I nest CTEs inside CTEs?

A CTE may contain its own WITH clause, though flat lists of CTEs read more clearly. Keep each block short and give it a descriptive name such as monthly_sales or latest_orders.

Key Points

• Subqueries nest a query inside WHERE, FROM, or SELECT.
• CTEs name each step with WITH and read from top to bottom.
• EXISTS checks for matches efficiently.
• Recursive CTEs walk hierarchies of unknown depth.
• Small named steps make long queries easier to test and maintain.