Common Table Expressions (CTEs) in Snowflake - Time & Space Complexity

Time Complexity: Common Table Expressions (CTEs)

O(n)

Understanding Time Complexity

When using Common Table Expressions (CTEs) in Snowflake, it's important to understand how the time to run queries grows as the data size increases.

We want to know how the number of operations changes when the input data gets bigger.

Scenario Under Consideration

Analyze the time complexity of this CTE query sequence.

WITH recent_orders AS (
  SELECT * FROM orders WHERE order_date >= DATEADD(day, -30, CURRENT_DATE())
),
customer_totals AS (
  SELECT customer_id, SUM(amount) AS total_spent FROM recent_orders GROUP BY customer_id
)
SELECT * FROM customer_totals WHERE total_spent > 1000;
    

This query uses two CTEs: one to filter recent orders, and another to sum spending per customer, then filters customers by total spent.

Identify Repeating Operations

Look at the main operations that happen repeatedly.

• Primary operation: Scanning the orders table and grouping by customer_id.
• How many times: The orders table is scanned once; grouping depends on number of customers in recent orders.

How Execution Grows With Input

As the number of recent orders grows, the scan and grouping take more time.

Input Size (n)	Approx. Api Calls/Operations
10	About 10 rows scanned and grouped
100	About 100 rows scanned and grouped
1000	About 1000 rows scanned and grouped

Pattern observation: The work grows roughly in direct proportion to the number of recent orders.

Final Time Complexity

Time Complexity: O(n)

This means the time to run the query grows linearly with the number of rows processed in the CTE.

Common Mistake

[X] Wrong: "CTEs always run once and do not affect query time as data grows."

[OK] Correct: CTEs are like temporary views; their cost depends on the data size they process, so bigger data means more work.

Interview Connect

Understanding how CTEs impact query time helps you design efficient data pipelines and write queries that scale well.

Self-Check

"What if the CTE included a join with another large table? How would the time complexity change?"