-- Basic CTE
WITH high_value_customers AS (
SELECT
user_id,
SUM(total) as lifetime_value
FROM orders
GROUP BY user_id
HAVING SUM(total) > 1000
)
SELECT
u.name,
u.email,
hvc.lifetime_value
FROM users u
INNER JOIN high_value_customers hvc ON u.id = hvc.user_id
ORDER BY hvc.lifetime_value DESC;
-- Multiple CTEs
WITH
monthly_sales AS (
SELECT
DATE_TRUNC('month', order_date) as month,
SUM(total) as total_sales
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
),
monthly_costs AS (
SELECT
DATE_TRUNC('month', expense_date) as month,
SUM(amount) as total_costs
FROM expenses
GROUP BY DATE_TRUNC('month', expense_date)
)
SELECT
COALESCE(s.month, c.month) as month,
COALESCE(s.total_sales, 0) as sales,
COALESCE(c.total_costs, 0) as costs,
COALESCE(s.total_sales, 0) - COALESCE(c.total_costs, 0) as profit
FROM monthly_sales s
FULL OUTER JOIN monthly_costs c ON s.month = c.month
ORDER BY month;
-- CTEs referencing other CTEs
WITH
active_users AS (
SELECT id, name, email
FROM users
WHERE last_login > CURRENT_DATE - INTERVAL '30 days'
),
user_orders AS (
SELECT
au.id,
au.name,
COUNT(o.id) as order_count
FROM active_users au
LEFT JOIN orders o ON au.id = o.user_id
GROUP BY au.id, au.name
)
SELECT *
FROM user_orders
WHERE order_count > 0
ORDER BY order_count DESC;
-- CTE for data transformation pipeline
WITH
cleaned_data AS (
SELECT
TRIM(LOWER(email)) as email,
name,
created_at
FROM raw_users
WHERE email IS NOT NULL
),
deduped_data AS (
SELECT DISTINCT ON (email)
email,
name,
created_at
FROM cleaned_data
ORDER BY email, created_at DESC
)
SELECT * FROM deduped_data;
-- Recursive CTE: Organization hierarchy
WITH RECURSIVE org_chart AS (
-- Base case: Top-level managers
SELECT
id,
name,
manager_id,
1 as level,
name as path
FROM employees
WHERE manager_id IS NULL
UNION ALL
-- Recursive case: Employees with managers
SELECT
e.id,
e.name,
e.manager_id,
oc.level + 1,
oc.path || ' > ' || e.name
FROM employees e
INNER JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT
REPEAT(' ', level - 1) || name as org_structure,
level,
path
FROM org_chart
ORDER BY path;
-- Generate series (date range)
WITH RECURSIVE date_series AS (
SELECT DATE '2024-01-01' as date
UNION ALL
SELECT date + INTERVAL '1 day'
FROM date_series
WHERE date < DATE '2024-12-31'
)
SELECT
ds.date,
COALESCE(SUM(o.total), 0) as daily_revenue
FROM date_series ds
LEFT JOIN orders o ON DATE(o.created_at) = ds.date
GROUP BY ds.date
ORDER BY ds.date;
-- Category tree traversal
WITH RECURSIVE category_tree AS (
-- Root categories
SELECT
id,
name,
parent_id,
1 as depth,
ARRAY[id] as path
FROM categories
WHERE parent_id IS NULL
UNION ALL
-- Child categories
SELECT
c.id,
c.name,
c.parent_id,
ct.depth + 1,
ct.path || c.id
FROM categories c
INNER JOIN category_tree ct ON c.parent_id = ct.id
WHERE NOT c.id = ANY(ct.path) -- Prevent cycles
)
SELECT
REPEAT(' ', depth - 1) || name as category_hierarchy,
depth,
path
FROM category_tree
ORDER BY path;
-- Find all subordinates
WITH RECURSIVE subordinates AS (
SELECT id, name, manager_id
FROM employees
WHERE id = 5 -- Starting employee
UNION ALL
SELECT e.id, e.name, e.manager_id
FROM employees e
INNER JOIN subordinates s ON e.manager_id = s.id
)
SELECT name, id
FROM subordinates;
-- Materialized CTE (PostgreSQL 12+)
WITH monthly_aggregates AS MATERIALIZED (
SELECT
DATE_TRUNC('month', order_date) as month,
COUNT(*) as order_count,
SUM(total) as total_revenue
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '1 year'
GROUP BY DATE_TRUNC('month', order_date)
)
SELECT * FROM monthly_aggregates
WHERE total_revenue > 10000;
