Difficulty: Advanced
Common Table Expressions (CTEs) are named temporary result sets defined using the WITH clause that exist only for the duration of a single query. They are one of the most important features for writing clean, maintainable, and debuggable analytical SQL. Instead of nesting subqueries inside subqueries (which quickly becomes unreadable), CTEs let you break complex queries into logical, named steps that can reference each other.
A CTE is defined as WITH cte_name AS (SELECT ...) followed by a main query that references cte_name. You can chain multiple CTEs separated by commas, where later CTEs can reference earlier ones. This creates a readable pipeline: first compute aggregates, then filter, then join with other data, then format the output. Each step has a meaningful name that documents its purpose.
Recursive CTEs are a special form that reference themselves, creating iterative computation within SQL. They consist of a base case (anchor member) and a recursive step that unions with the previous iteration. Common use cases include traversing hierarchical data (org charts, category trees), generating date sequences, and computing iterative calculations. The recursion terminates when the recursive step returns no new rows.
Analytical patterns like year-over-year comparison, running totals, moving averages, and cohort analysis all benefit enormously from CTEs. By separating the data preparation steps from the final calculation, CTEs make these complex patterns manageable. For example, a year-over-year comparison might use one CTE for current year data, another for previous year data, and a final query that joins them to compute growth rates.
# Simulating a CTE pipeline in Python
# SQL equivalent:
# WITH monthly_totals AS (
# SELECT month, SUM(revenue) as total FROM sales GROUP BY month
# ),
# with_rankings AS (
# SELECT *, RANK() OVER (ORDER BY total DESC) as rank FROM monthly_totals
# )
# SELECT * FROM with_rankings WHERE rank <= 3;
sales = [
{'month': 'Jan', 'product': 'A', 'revenue': 5000},
{'month': 'Jan', 'product': 'B', 'revenue': 3000},
{'month': 'Feb', 'product': 'A', 'revenue': 7000},
{'month': 'Feb', 'product': 'B', 'revenue': 2000},
{'month': 'Mar', 'product': 'A', 'revenue': 4000},
{'month': 'Mar', 'product': 'B', 'revenue': 6000},
{'month': 'Apr', 'product': 'A', 'revenue': 8000},
{'month': 'Apr', 'product': 'B', 'revenue': 4000},
]
# CTE 1: monthly_totals
monthly_totals = {}
for s in sales:
monthly_totals[s['month']] = monthly_totals.get(s['month'], 0) + s['revenue']
print("CTE 1 - monthly_totals:")
for m, t in monthly_totals.items():
print(f" {m}: ${t:,}")
# CTE 2: with_rankings
ranked = sorted(monthly_totals.items(), key=lambda x: x[1], reverse=True)
print("\nCTE 2 - with_rankings (top 3):")
for rank, (month, total) in enumerate(ranked[:3], 1):
print(f" Rank {rank}: {month} = ${total:,}")This demonstrates the CTE pipeline pattern: first CTE aggregates sales by month, second CTE ranks them. The final query filters to top 3. Each CTE is a named, reusable step that makes the logic clear and debuggable. In a real database, this executes as a single optimized query.
# SQL pattern:
# WITH current_year AS (SELECT ... WHERE year = 2024),
# previous_year AS (SELECT ... WHERE year = 2023)
# SELECT c.month, c.revenue, p.revenue as prev, growth_pct
# FROM current_year c JOIN previous_year p ON c.month = p.month
revenue_data = [
{'year': 2023, 'quarter': 'Q1', 'revenue': 100000},
{'year': 2023, 'quarter': 'Q2', 'revenue': 120000},
{'year': 2023, 'quarter': 'Q3', 'revenue': 110000},
{'year': 2023, 'quarter': 'Q4', 'revenue': 140000},
{'year': 2024, 'quarter': 'Q1', 'revenue': 115000},
{'year': 2024, 'quarter': 'Q2', 'revenue': 135000},
{'year': 2024, 'quarter': 'Q3', 'revenue': 125000},
{'year': 2024, 'quarter': 'Q4', 'revenue': 160000},
]
# CTE: Split by year
curr = {r['quarter']: r['revenue'] for r in revenue_data if r['year'] == 2024}
prev = {r['quarter']: r['revenue'] for r in revenue_data if r['year'] == 2023}
print("Year-over-Year Comparison:")
print(f"{'Quarter':<8} {'2023':>10} {'2024':>10} {'Growth':>8}")
print("-" * 38)
for q in ['Q1', 'Q2', 'Q3', 'Q4']:
p_rev = prev[q]
c_rev = curr[q]
growth = (c_rev - p_rev) / p_rev * 100
print(f"{q:<8} ${p_rev:>9,} ${c_rev:>9,} {growth:>+7.1f}%")
total_prev = sum(prev.values())
total_curr = sum(curr.values())
total_growth = (total_curr - total_prev) / total_prev * 100
print("-" * 38)
print(f"{'Total':<8} ${total_prev:>9,} ${total_curr:>9,} {total_growth:>+7.1f}%")The YoY pattern uses two CTEs to separate current and previous year data, then joins them on the time dimension (quarter). Growth is computed as (current - previous) / previous * 100. This pattern is the standard approach for any period-over-period comparison in business analytics.
# Simulating cumulative and moving window calculations
weekly_data = [
{'week': 1, 'sales': 120},
{'week': 2, 'sales': 150},
{'week': 3, 'sales': 90},
{'week': 4, 'sales': 200},
{'week': 5, 'sales': 170},
{'week': 6, 'sales': 130},
]
window_size = 3
print(f"{'Week':>5} {'Sales':>6} {'Running':>9} {'3wk Avg':>8}")
print("-" * 30)
running = 0
for i, w in enumerate(weekly_data):
running += w['sales']
# Moving average (last 3 weeks)
start = max(0, i - window_size + 1)
window = [weekly_data[j]['sales'] for j in range(start, i + 1)]
moving_avg = sum(window) / len(window)
print(f"{w['week']:>5} ${w['sales']:>5} ${running:>8} ${moving_avg:>7.0f}")The running total (SUM OVER ORDER BY) accumulates all previous values. The 3-week moving average (AVG OVER ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) smooths out weekly fluctuations. Weeks 1-2 use fewer than 3 values since there is not enough history yet. These are essential time-series analytics patterns.
# Demonstrating CTE SQL syntax patterns
print("Pattern 1: Simple CTE")
print("""
WITH active_users AS (
SELECT user_id, COUNT(*) as login_count
FROM logins
WHERE login_date >= '2024-01-01'
GROUP BY user_id
HAVING COUNT(*) >= 5
)
SELECT u.name, a.login_count
FROM users u
JOIN active_users a ON u.id = a.user_id
ORDER BY a.login_count DESC;
""")
print("Pattern 2: Chained CTEs")
print("""
WITH monthly_sales AS (
SELECT DATE_TRUNC('month', sale_date) as month,
SUM(amount) as total
FROM sales
GROUP BY 1
),
with_growth AS (
SELECT month, total,
LAG(total) OVER (ORDER BY month) as prev_total,
total - LAG(total) OVER (ORDER BY month) as growth
FROM monthly_sales
)
SELECT month, total, prev_total,
ROUND(growth * 100.0 / prev_total, 1) as growth_pct
FROM with_growth
WHERE prev_total IS NOT NULL;
""")Pattern 1 shows a simple CTE that finds active users (5+ logins), then joins with the users table for names. Pattern 2 chains two CTEs: the first computes monthly totals, the second adds LAG-based growth, and the final query filters and formats. These patterns are the foundation of production analytics queries.
CTEs, recursive queries, running totals, year-over-year