Objective

You are given a table of Amazon employees with their corresponding managers. Each row represents a reporting relationship between an employee and their manager.

Table Schema:

The employees table:

Task Requirements:

• A manager is an employee who has at least one direct report
• A senior manager is an employee who manages at least one manager, but none of their direct reports are senior managers themselves
• Find all senior managers and count their direct reports
• An employee can report to two senior managers
• Order the results by direct report count in descending order, then by name alphabetically

Output columns: senior_manager_name, direct_report_count

WITH managers AS (
    SELECT DISTINCT manager_id AS employee_id
    FROM employees
    WHERE manager_id IS NOT NULL
),
manages_managers AS (
    SELECT DISTINCT e.manager_id AS employee_id
    FROM employees e
    JOIN managers m ON e.employee_id = m.employee_id
    WHERE e.manager_id IS NOT NULL
)
SELECT
    mgr.employee_name AS senior_manager_name,
    COUNT(*) AS direct_report_count
FROM manages_managers mm
JOIN employees mgr ON mm.employee_id = mgr.employee_id
JOIN employees e ON e.manager_id = mm.employee_id
WHERE NOT EXISTS (
    SELECT 1
    FROM employees sub
    JOIN manages_managers mm2 ON sub.employee_id = mm2.employee_id
    WHERE sub.manager_id = mm.employee_id
)
GROUP BY mgr.employee_name
ORDER BY direct_report_count DESC, senior_manager_name;

Explanation

Step 1: Identify all managers

The first CTE collects all distinct manager_id values to identify every employee who has at least one direct report.

SELECT DISTINCT manager_id AS employee_id
FROM employees
WHERE manager_id IS NOT NULL

Step 2: Identify managers who manage other managers

The second CTE joins the employees table with the managers CTE to find employees whose own direct reports are themselves managers.

SELECT DISTINCT e.manager_id AS employee_id
FROM employees e
JOIN managers m ON e.employee_id = m.employee_id
WHERE e.manager_id IS NOT NULL

Step 3: Filter to senior managers using NOT EXISTS

A senior manager is in manages_managers but whose direct reports are NOT also in manages_managers. The NOT EXISTS subquery verifies this.

WHERE NOT EXISTS (
    SELECT 1
    FROM employees sub
    JOIN manages_managers mm2 ON sub.employee_id = mm2.employee_id
    WHERE sub.manager_id = mm.employee_id
)

Step 4: Count direct reports and order results

Joins back to employees to get the senior manager's name and counts all their direct reports with COUNT(*), ordered by count descending.