SQL Recursive CTEs for Hierarchies

-- Database schema for SQL Recursive CTEs for Hierarchies

CREATE TABLE departments (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE,
    budget DECIMAL(12, 2) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    department_id INT REFERENCES departments(id),
    salary DECIMAL(10, 2) NOT NULL,
    hire_date DATE NOT NULL,
    status VARCHAR(20) DEFAULT 'active'
);

CREATE TABLE projects (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    department_id INT REFERENCES departments(id),
    budget DECIMAL(12, 2),
    start_date DATE,
    end_date DATE,
    status VARCHAR(20) DEFAULT 'planning'
);

CREATE INDEX idx_emp_dept ON employees(department_id);
CREATE INDEX idx_emp_status ON employees(status);
CREATE INDEX idx_proj_dept ON projects(department_id);

-- Department summary with employee count and avg salary
SELECT
    d.name AS department,
    COUNT(e.id) AS employee_count,
    ROUND(AVG(e.salary), 2) AS avg_salary,
    MIN(e.salary) AS min_salary,
    MAX(e.salary) AS max_salary,
    SUM(e.salary) AS total_payroll
FROM departments d
LEFT JOIN employees e ON d.id = e.department_id
WHERE e.status = 'active'
GROUP BY d.id, d.name
HAVING COUNT(e.id) > 0
ORDER BY avg_salary DESC;

-- Ranking employees by salary within department
WITH ranked AS (
    SELECT
        e.*,
        d.name AS department_name,
        ROW_NUMBER() OVER (PARTITION BY e.department_id ORDER BY e.salary DESC) AS salary_rank,
        ROUND(e.salary / SUM(e.salary) OVER (PARTITION BY e.department_id) * 100, 1) AS pct_of_dept
    FROM employees e
    JOIN departments d ON e.department_id = d.id
    WHERE e.status = 'active'
)
SELECT
    department_name,
    first_name || ' ' || last_name AS employee_name,
    salary,
    salary_rank,
    pct_of_dept || '%' AS salary_share
FROM ranked
WHERE salary_rank <= 5
ORDER BY department_name, salary_rank;

-- Check query execution plan
EXPLAIN ANALYZE
SELECT e.*, d.name
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE e.salary > 75000
  AND e.status = 'active';

-- Create covering index for common query pattern
CREATE INDEX idx_emp_covering
ON employees(status, salary)
INCLUDE (first_name, last_name, department_id);