Slide 48
Slide 48 text
CTE Implementation Examples
Basic CTE Structure
WITH orders_summary AS (
SELECT
customer_id,
COUNT(*) as order_count,
SUM(amount) as total_spent
FROM orders
GROUP BY customer_id
)
SELECT c.name, os.order_count, os.total_spent
FROM customers c
JOIN orders_summary os
ON c.customer_id = os.customer_id
WHERE os.total_spent > 1000
ORDER BY os.total_spent DESC;
Multiple CTEs
WITH
top_products AS (
SELECT product_id, SUM(quantity) as
units_sold
FROM order_items
GROUP BY product_id
ORDER BY units_sold DESC
LIMIT 10
),
product_revenue AS (
SELECT product_id, SUM(price * quantity)
as revenue
FROM order_items
GROUP BY product_id
)
SELECT p.name, tp.units_sold, pr.revenue
FROM top_products tp
JOIN products p ON tp.product_id =
p.product_id
JOIN product_revenue pr ON p.product_id =
pr.product_id
ORDER BY tp.units_sold DESC;
Recursive CTE
WITH RECURSIVE employee_hierarchy AS (
-- Base case: top-level employees
SELECT id, name, manager_id, 1 as level
FROM employees
WHERE manager_id IS NULL
UNION ALL
-- Recursive case: add employees
-- who report to someone in the CTE
SELECT e.id, e.name, e.manager_id,
eh.level + 1
FROM employees e
JOIN employee_hierarchy eh
ON e.manager_id = eh.id
)
SELECT id, name, level
FROM employee_hierarchy
ORDER BY level, name;