Recursive CTEs solve tree and graph problems that would require complex application code or multiple queries. Real examples: LinkedIn "People You May Know" (network traversal), Amazon category navigation (product hierarchy), Slack thread replies (nested comments), GitHub repository dependencies (dependency graphs). Every hierarchical structure in production databases uses recursive CTEs.

LinkedIn - People You May Know

Social network recommendation engine finding connections up to 3 degrees away with mutual friend counts

Powers recommendation engines at LinkedIn, Facebook. Finds connections through mutual friends. Scales to millions of users with proper indexing.

Find 2nd and 3rd degree connections with mutual friends

WITH RECURSIVE network AS (

SELECT user_id, friend_id, 1 AS degree, ARRAY[user_id, friend_id] AS path

FROM friendships WHERE user_id = 42

UNION

SELECT n.user_id, f.friend_id, n.degree + 1, n.path || f.friend_id

FROM network n JOIN friendships f ON n.friend_id = f.user_id

WHERE n.degree < 3 AND NOT f.friend_id = ANY(n.path) AND f.friend_id != 42

)

SELECT u.name, n.degree, COUNT(*) AS mutual_friends

FROM network n JOIN users u ON n.friend_id = u.id

WHERE n.degree > 1 GROUP BY u.name, n.degree ORDER BY mutual_friends DESC LIMIT 20;

All

Amazon - Product Category Navigation

E-commerce category tree with breadcrumbs and subcategory counts

Generates navigation menus, breadcrumbs, category filters. Used by Amazon, eBay, Shopify. Enables drill-down browsing with product counts per category.

Build category tree with product counts

WITH RECURSIVE category_tree AS (

SELECT id, name, parent_id, 0 AS depth, name AS breadcrumb

FROM categories WHERE name = 'Electronics'

UNION ALL

SELECT c.id, c.name, c.parent_id, ct.depth + 1, ct.breadcrumb || ' > ' || c.name

FROM categories c JOIN category_tree ct ON c.parent_id = ct.id WHERE ct.depth < 5

)

SELECT ct.*, COUNT(p.id) AS product_count

FROM category_tree ct LEFT JOIN products p ON ct.id = p.category_id

GROUP BY ct.id, ct.name, ct.depth, ct.breadcrumb ORDER BY ct.breadcrumb;

All

Manufacturing - Bill of Materials (BOM)

Calculate total parts needed for production run with cost rollup

Critical for manufacturing (Boeing, Tesla). Calculates material requirements, costs, lead times. Prevents production delays from missing parts.

Explode BOM with quantities and costs

WITH RECURSIVE bom AS (

SELECT id, name, 100 AS qty, unit_cost, 0 AS level FROM parts WHERE name = 'Bicycle'

UNION ALL

SELECT p.id, p.name, b.qty * bp.qty_per, p.unit_cost, b.level + 1