Hierarchical data is everywhere in business applications. Organization charts show reporting structures with unlimited management levels. E-commerce sites use category trees (Electronics → Computers → Laptops → Gaming Laptops). Manufacturing tracks bill-of-materials with nested components. File systems organize folders within folders. Social networks map follower relationships. Recursive CTEs handle all these parent-child structures efficiently.

LinkedIn: Company Organization Chart

LinkedIn company pages display org charts showing reporting structure. Users can explore the hierarchy, see management levels, and understand company structure. Recursive CTEs build the complete hierarchy from CEO down to individual contributors.

Enables users to understand company structure, identify decision-makers, and explore career paths. Critical for recruiting, networking, and sales prospecting. Handles massive org charts (Google: 150K+ employees) with sub-second response times through indexing and caching.

Build complete org chart with levels, paths, and team sizes

-- LinkedIn org chart query

WITH RECURSIVE org_hierarchy AS (

-- Base: C-level executives (no manager)

SELECT

e.employee_id,

e.full_name,

e.title,

e.manager_id,

e.department,

1 as level,

e.full_name as reporting_path,

CAST(e.full_name AS VARCHAR(2000)) as sort_path

FROM employees e

WHERE e.manager_id IS NULL

AND e.status = 'active'

UNION ALL

-- Recursive: All employees reporting up the chain

SELECT

e.employee_id,

e.full_name,

e.title,

e.manager_id,

e.department,

oh.level + 1,

oh.reporting_path || ' → ' || e.full_name,

oh.sort_path || '|' || e.full_name

FROM employees e

JOIN org_hierarchy oh ON e.manager_id = oh.employee_id

WHERE e.status = 'active'

AND oh.level < 15 -- Max 15 management levels

)

SELECT

oh.employee_id,

oh.level,

oh.full_name,