-- Create hierarchical table (org chart)
CREATE TABLE employees (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
title VARCHAR(100),
manager_id INT REFERENCES employees(id),
salary DECIMAL(10,2)
);
INSERT INTO employees (id, name, title, manager_id, salary) VALUES
(1, 'Alice CEO', 'CEO', NULL, 200000),
(2, 'Bob VP', 'VP Engineering', 1, 150000),
(3, 'Carol VP', 'VP Sales', 1, 150000),
(4, 'Dave Manager', 'Engineering Manager', 2, 120000),
(5, 'Eve Manager', 'Sales Manager', 3, 120000),
(6, 'Frank Dev', 'Senior Developer', 4, 100000),
(7, 'Grace Dev', 'Developer', 4, 80000),
(8, 'Henry Sales', 'Sales Rep', 5, 70000);
-- Recursive CTE: Find all subordinates of employee 2
WITH RECURSIVE subordinates AS (
-- Base case: Start with employee 2
SELECT id, name, title, manager_id, 1 AS level
FROM employees
WHERE id = 2
UNION ALL
-- Recursive case: Find employees managed by previous results
SELECT e.id, e.name, e.title, e.manager_id, s.level + 1
FROM employees e
JOIN subordinates s ON e.manager_id = s.id
)
SELECT
id,
REPEAT(' ', level - 1) || name AS indented_name,
title,
level
FROM subordinates
ORDER BY level, name;
-- Find all managers above an employee (ancestors)
WITH RECURSIVE managers AS (
-- Base case: Start with specific employee
SELECT id, name, title, manager_id, 0 AS level
FROM employees
WHERE id = 6 -- Frank
UNION ALL
-- Recursive case: Find manager of previous result
SELECT e.id, e.name, e.title, e.manager_id, m.level + 1
FROM employees e
JOIN managers m ON e.id = m.manager_id
)
SELECT name, title, level
FROM managers
ORDER BY level DESC;
-- Full org chart from top down
WITH RECURSIVE org_chart AS (
-- Base case: CEO (no manager)
SELECT
id,
name,
title,
manager_id,
1 AS level,
name::TEXT AS path
FROM employees
WHERE manager_id IS NULL
UNION ALL
-- Recursive case: Employees at next level
SELECT
e.id,
e.name,
e.title,
e.manager_id,
oc.level + 1,
oc.path || ' > ' || e.name
FROM employees e
JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT
level,
REPEAT(' ', level - 1) || name AS indented_name,
title,
path
FROM org_chart
ORDER BY path;
-- Aggregate: Total salary by department (including subordinates)
WITH RECURSIVE dept_salaries AS (
SELECT
id,
name,
salary,
salary AS total_dept_salary
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT
e.id,
e.name,
e.salary,
e.salary + ds.total_dept_salary
FROM employees e
JOIN dept_salaries ds ON e.manager_id = ds.id
)
SELECT name, total_dept_salary
FROM dept_salaries
ORDER BY total_dept_salary DESC;
-- Category tree
CREATE TABLE categories (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
parent_id INT REFERENCES categories(id)
);
INSERT INTO categories (id, name, parent_id) VALUES
(1, 'Electronics', NULL),
(2, 'Computers', 1),
(3, 'Phones', 1),
(4, 'Laptops', 2),
(5, 'Desktops', 2),
(6, 'Smartphones', 3),
(7, 'Feature Phones', 3);
-- Find all subcategories
WITH RECURSIVE subcategories AS (
SELECT id, name, parent_id, 1 AS level
FROM categories
WHERE id = 1 -- Electronics
UNION ALL
SELECT c.id, c.name, c.parent_id, sc.level + 1
FROM categories c
JOIN subcategories sc ON c.parent_id = sc.id
)
SELECT
REPEAT(' ', level - 1) || name AS category_tree,
level
FROM subcategories
ORDER BY level, name;
-- Find category path (breadcrumbs)
WITH RECURSIVE category_path AS (
SELECT id, name, parent_id, name::TEXT AS path
FROM categories
WHERE id = 6 -- Smartphones
UNION ALL
SELECT c.id, c.name, c.parent_id, c.name || ' > ' || cp.path
FROM categories c
JOIN category_path cp ON c.id = cp.parent_id
)
SELECT path
FROM category_path
WHERE parent_id IS NULL;
-- Cycle detection (prevent infinite loops)
CREATE TABLE graph (
from_node INT,
to_node INT
);
INSERT INTO graph VALUES
(1, 2), (2, 3), (3, 4), (4, 2); -- Has cycle: 2->3->4->2
WITH RECURSIVE graph_traversal AS (
-- Base case
SELECT
from_node,
to_node,
ARRAY[from_node, to_node] AS path,
false AS is_cycle
FROM graph
WHERE from_node = 1
UNION ALL
-- Recursive case with cycle detection
SELECT
g.from_node,
g.to_node,
gt.path || g.to_node,
g.to_node = ANY(gt.path) AS is_cycle
FROM graph g
JOIN graph_traversal gt ON g.from_node = gt.to_node
WHERE NOT gt.is_cycle -- Stop traversing cycles
AND array_length(gt.path, 1) < 10 -- Depth limit
)
SELECT
path,
CASE WHEN is_cycle THEN 'CYCLE DETECTED' ELSE 'OK' END AS status
FROM graph_traversal;
-- Shortest path in graph
WITH RECURSIVE shortest_path AS (
SELECT
from_node,
to_node,
ARRAY[from_node, to_node] AS path,
1 AS distance
FROM graph
WHERE from_node = 1
UNION ALL
SELECT
g.from_node,
g.to_node,
sp.path || g.to_node,
sp.distance + 1
FROM graph g
JOIN shortest_path sp ON g.from_node = sp.to_node
WHERE NOT (g.to_node = ANY(sp.path))
AND sp.distance < 10
)
SELECT DISTINCT ON (to_node)
to_node,
path,
distance
FROM shortest_path
WHERE to_node = 4 -- Target node
ORDER BY to_node, distance
LIMIT 1;
-- Bill of Materials (BOM)
CREATE TABLE parts (
part_id INT PRIMARY KEY,
part_name VARCHAR(100),
unit_cost DECIMAL(10,2)
);
CREATE TABLE assembly (
parent_part_id INT REFERENCES parts(part_id),
child_part_id INT REFERENCES parts(part_id),
quantity INT,
PRIMARY KEY (parent_part_id, child_part_id)
);
INSERT INTO parts VALUES
(1, 'Bicycle', 0),
(2, 'Frame', 100),
(3, 'Wheel', 50),
(4, 'Tire', 15),
(5, 'Rim', 25),
(6, 'Spoke', 1);
INSERT INTO assembly VALUES
(1, 2, 1), -- Bicycle has 1 Frame
(1, 3, 2), -- Bicycle has 2 Wheels
(3, 4, 1), -- Wheel has 1 Tire
(3, 5, 1), -- Wheel has 1 Rim
(5, 6, 36); -- Rim has 36 Spokes
-- Exploded BOM (all parts needed)
WITH RECURSIVE bom AS (
-- Base case: Top-level product
SELECT
child_part_id AS part_id,
quantity,
1 AS level
FROM assembly
WHERE parent_part_id = 1 -- Bicycle
UNION ALL
-- Recursive case: Sub-assemblies
SELECT
a.child_part_id,
b.quantity * a.quantity,
b.level + 1
FROM assembly a
JOIN bom b ON a.parent_part_id = b.part_id
)
SELECT
p.part_name,
SUM(b.quantity) AS total_quantity,
p.unit_cost,
SUM(b.quantity * p.unit_cost) AS total_cost,
MAX(b.level) AS max_depth
FROM bom b
JOIN parts p ON b.part_id = p.part_id
GROUP BY p.part_name, p.unit_cost
ORDER BY max_depth, p.part_name;
-- Fibonacci sequence (non-hierarchical recursion)
WITH RECURSIVE fibonacci(n, fib, next_fib) AS (
SELECT 1, 0::BIGINT, 1::BIGINT
UNION ALL
SELECT n + 1, next_fib, fib + next_fib
FROM fibonacci
WHERE n < 20
)
SELECT n, fib FROM fibonacci;
-- Generate date series
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-01-31'
)
SELECT date, EXTRACT(DOW FROM date) AS day_of_week
FROM date_series;
-- Flatten JSON tree
CREATE TABLE json_tree (
id SERIAL PRIMARY KEY,
data JSONB
);
INSERT INTO json_tree (data) VALUES
('{
"name": "root",
"children": [
{"name": "child1", "children": [{"name": "grandchild1"}]},
{"name": "child2"}
]
}');
WITH RECURSIVE flatten AS (
SELECT
data ->> 'name' AS name,
data -> 'children' AS children,
1 AS level
FROM json_tree
UNION ALL
SELECT
elem ->> 'name',
elem -> 'children',
f.level + 1
FROM flatten f,
jsonb_array_elements(f.children) elem
WHERE f.children IS NOT NULL
)
SELECT name, level
FROM flatten;
