SQL Common Table Expressions (CTEs) and Recursive CTEs: Advanced Guide

SQL CTEs fundamentals (Common Table Expressions) are powerful tools for organizing complex queries. A CTE is a temporary named result set that exists only within a single SQL statement, making large queries readable, maintainable, and reusable.

Understanding SQL CTEs Fundamentals

SQL CTEs fundamentals start with the WITH clause. A CTE is a named query that precedes a main query. Instead of nesting subqueries or repeating logic, CTEs allow you to name intermediate results and reference them clearly.

Multiple CTEs: Building Blocks for Complex Logic

SQL CTEs fundamentals include chaining multiple CTEs together. Define CTE1, then CTE2 referencing CTE1, then CTE3 referencing CTE2, and so on. This is how senior engineers build complex analyses step-by-step instead of nested nightmares.

CTE Scope and Referencing

SQL CTEs fundamentals require understanding scope. A CTE is visible to all subsequent queries in the same statement but not to CTEs defined above it. CTE1 cant reference CTE2 if CTE2 is defined after CTE1. You must define CTEs in dependency order (bottom-up).

Recursive CTEs: For Hierarchical Data

SQL CTEs fundamentals include recursive CTEs, which reference themselves. Recursive CTEs solve tree/hierarchy problems: organizational charts, bill-of-materials (products composed of sub-products), file systems, etc.

Syntax: WITH RECURSIVE cte_name AS (anchor_query UNION ALL recursive_query) SELECT * FROM cte_name. The anchor_query is the base case (starting rows). The recursive_query references the CTE itself, operating on previous iterations.

When to Use CTEs vs Subqueries vs Window Functions

SQL CTEs fundamentals teach choosing the right tool. Use CTEs when: query has multiple logical steps, you need to reference the same intermediate result multiple times, readability matters. Use subqueries when: only one intermediate calculation, query is simple. Use window functions when: you need analytical calculations (ranking, running totals) without reducing rows.

CTE Performance Considerations

SQL CTEs fundamentals include understanding that CTEs are not always faster. Some databases materialize CTEs (compute once, reuse), others recompute them for each reference. Check your database documentation.

5 Complete Interview Questions with Solutions

Question 1: Multiple CTEs for Step-by-Step Logic

Find top 10% of customers by lifetime value using multiple CTEs: 1) customer LTV, 2) percentile threshold, 3) final result.

WITH customer_ltv AS (...),
percentile_calc AS (...)
SELECT * FROM ... WHERE ...;

Explanation: CTE1 calculates lifetime value per customer. CTE2 calculates 90th percentile. Main query joins both CTEs to filter top 10%. SQL CTEs fundamentals make this readable.

Google – Senior Data AnalystAmazon – Analytics EngineerMeta – Senior Analytics Engineer

Question 2: Recursive CTE for Hierarchy

Build an org chart showing each employee, their manager, and hierarchy level using recursive CTE.

WITH RECURSIVE org_hierarchy AS (
  SELECT ... WHERE manager_id IS NULL
  UNION ALL
  SELECT ... FROM employees e
  JOIN org_hierarchy oh ON e.manager_id = oh.employee_id
)
SELECT * FROM org_hierarchy;

Explanation: Anchor query finds CEO (no manager). Recursive part finds employees of each level. UNION ALL combines all levels. SQL CTEs fundamentals with recursion solve hierarchies elegantly.

Microsoft – Analytics EngineerGoogle – BI EngineerAirbnb – Data Analyst

Question 3: CTE with Window Functions

Rank customers by revenue within each region. Use CTE to compute regional revenue, then window function for ranking.

WITH customer_regional_revenue AS (...)
SELECT ...,
       ROW_NUMBER() OVER (PARTITION BY region ORDER BY revenue DESC)
FROM customer_regional_revenue;

Explanation: CTE aggregates revenue by customer-region. Main query adds window function ranking. SQL CTEs fundamentals + window functions solve complex analytics elegantly.

LinkedIn – Senior Analytics EngineerFlipkart – BI Engineer

Question 4: CTE Reuse

Find pairs of customers in same segment with different lifetime values. Use CTE twice (aliased differently).

WITH customer_segments AS (...)
SELECT cs1.customer_id, cs2.customer_id, cs1.segment, cs1.ltv, cs2.ltv
FROM customer_segments cs1
JOIN customer_segments cs2 ON cs1.segment = cs2.segment
WHERE cs1.customer_id < cs2.customer_id AND cs1.ltv <> cs2.ltv;

Explanation: CTE is defined once but referenced twice (cs1 and cs2). SQL CTEs fundamentals show why this is cleaner than two separate subqueries.

OYO – Senior Data AnalystStripe – Analytics EngineerUber – Senior BI Engineer

Question 5: Recursive CTE for Bill of Materials

Find all products that go into making a final product (bill-of-materials recursion) with unlimited depth.

WITH RECURSIVE bom AS (
  SELECT product_id, component_id, 1 AS level
  FROM assembly WHERE product_id = LAPTOP
  UNION ALL
  SELECT a.product_id, a.component_id, bom.level + 1
  FROM assembly a JOIN bom ON a.product_id = bom.component_id
  WHERE bom.level < 20
)
SELECT DISTINCT component_id, level FROM bom;

Explanation: Anchor finds direct components. Recursive part finds sub-components. SQL CTEs fundamentals with recursion solve supply chain problems elegantly.

Amazon – Analytics EngineerGoogle – Senior BI Engineer

15 Practice Questions (Test Yourself)

Question 6: Convert nested subqueries (4 levels deep) to multiple CTEs (CTE1→CTE2→CTE3→CTE4). What readability improvement gained from 5000-char query?

Google – Analytics EngineerMicrosoft – BI Engineer

Question 7: CTE1 references CTE2 defined after it (wrong order). PostgreSQL throws error. How would you reorder CTE dependencies for 3 CTEs?

Meta – Senior Data AnalystFlipkart – Analytics Engineer

Question 8: Regular CTE vs Recursive CTE: 100-level org hierarchy, cycles possible. Why is recursion necessary and how do you prevent infinite loops?

Amazon – Senior Analytics EngineerAirbnb – BI EngineerLinkedIn – Data Analyst

Question 9: CTE computed once then cached OR recomputed for each reference? 1M-row CTE referenced 3x. How does materialization affect 10s query vs 1s baseline?

Google – Senior BI EngineerOYO – Analytics Engineer

Question 10: Define 5 CTEs but only 2 actually used. Does unused CTE execution cost? How does database optimizer handle unreferenced CTEs?

Stripe – Senior Data AnalystMeta – BI EngineerUber – Analytics Engineer

Question 11: CTE1 = 10K rows, CTE2 = joins CTE1 100x. Result: 1M rows. Trace dependency order: which CTE executes first and why?

Amazon – Analytics EngineerGoogle – Senior Analytics Engineer

Question 12: PostgreSQL materializes CTEs, MySQL doesnt (sometimes). Same query runs 5s vs 15s. Whats the CTE strategy difference across databases?

Microsoft – Senior Analytics EngineerFlipkart – Data AnalystLinkedIn – Senior BI Engineer

Question 13: Recursive CTE generates all dates between 2024-01-01 and 2026-12-31 (1095 days) without looping. Anchor query and recursion strategy?

Google – Analytics EngineerAmazon – BI Engineer

Question 14: CTE aliased twice (cs1, cs2) then self-joined on segment. 50K unique segments × avg 20 customers. Expected output rows and performance?

Airbnb – Senior Data AnalystOYO – BI EngineerStripe – Analytics Engineer

Question 15: CTE1 has GROUP BY + HAVING. Move filter to CTE or keep in main SELECT? 100M rows, filtering to 1M. Optimization difference?

Meta – Analytics EngineerMicrosoft – Senior BI Engineer

Question 16: 3-level recursive CTE for org chart (CEO→managers→ICs). 5K employees, avg 5 direct reports. Anchor and termination condition?

Google – Senior Analytics EngineerAmazon – Data AnalystLinkedIn – Senior Analytics Engineer

Question 17: Same result: (a) CTE + window function, (b) subquery + window function, (c) self-join. Readability, performance, maintenance trade-offs?

Flipkart – Senior Analytics EngineerUber – BI Engineer

Question 18: CTE consolidates 3 data sources via UNION (system_a_orders, system_b_orders, legacy_orders). Handle schema differences, 10M total rows?

Meta – Senior Data AnalystMicrosoft – Analytics EngineerAirbnb – BI Engineer

Question 19: Recursive CTE generates first 100 Fibonacci numbers (F(n) = F(n-1) + F(n-2)). Anchor (1, 1) and recursive logic for iteration tracking?

Google – BI EngineerAmazon – Senior Analytics Engineer

Question 20: Recursive CTE with multiple UNION ALL branches (e.g., left AND right child paths in tree). How would cycles/duplicates affect result set size (10K nodes)?

LinkedIn – Senior Data AnalystOYO – Senior BI EngineerStripe – Analytics Engineer

Ready to Master SQL CTEs & All Analytics Concepts?

These 20 practice questions are just the tip of the iceberg. Get our comprehensive Data Analytics: Ace All The Interview Concepts course.