Loading Academy Hub...
Master modular SQL query design: non-recursive CTEs for readable ETL, optimizer materialization hints (MATERIALIZED vs NOT MATERIALIZED), and WITH RECURSIVE for organizational hierarchies.
[!NOTE] Common Table Expressions (CTEs) define temporary named result sets within a single SQL statement. They replace tangled subquery nests, improve readability, and enable recursive traversal of hierarchical graph structures.
In modern SQL optimizers (PostgreSQL 12+, Snowflake, BigQuery):
AS MATERIALIZED): The optimizer calculates the CTE once, caches the result in temporary memory/disk, and scans it multiple times.SELECT combined via UNION ALL with a self-referencing step joined on parent keys. Always cap recursion depth or guard cyclic edges (depth < 50) to avoid infinite loops on dirty graph data.DATEADD(day, 1, dt)) or warehouse GENERATE_SERIES / EXPLODE(SEQUENCE(...)) to zero-fill sparse dates before computing moving averages so gaps never distort 7-day rolling windows.Review the production code, company interview Q&As, incident traces, and comprehension quiz inside this lesson.
-- Traverse organizational hierarchy to find employee reporting chain and depth
WITH RECURSIVE org_hierarchy AS (
-- Anchor Member: Top-level CEO / Executive
SELECT
employee_id,
manager_id,
employee_name,
1 AS org_level,
CAST(employee_name AS VARCHAR(500)) AS reporting_chain
FROM employees
WHERE manager_id IS NULL
UNION ALL
-- Recursive Member: Join subordinate employees to previous level
SELECT
e.employee_id,
e.manager_id,
e.employee_name,
h.org_level + 1,
CAST(h.reporting_chain || ' -> ' || e.employee_name AS VARCHAR(500))
FROM employees e
JOIN org_hierarchy h ON e.manager_id = h.employee_id
)
SELECT * FROM org_hierarchy ORDER BY org_level, employee_id;ERROR: 54001: infinite recursion detected in recursive query. Target table has circular reference.