SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE employees (
 [employee] nvarchar(30), 
 [manager] nvarchar(30)
);
INSERT INTO employees
    ([employee], [manager])
VALUES
    ('Robert', null),
    ('Julian', 'Robert'),
    ('Sophie', 'Robert'),
    ('Lucy', 'Julian'),
    ('John', 'Lucy');
5 rows affected

WITH CTE_Employees AS (
  SELECT employee
  FROM employees
  GROUP BY employee
)
, RCTE_Managers AS (
  SELECT
    manager as base_manager
  , 1 as depth
  , employee
  , manager as direct_manager
  FROM employees
  WHERE manager IS NOT NULL
  
  UNION ALL
  
  SELECT
    cte.base_manager
  , cte.depth + 1
  , emp.employee
  , emp.manager
  FROM RCTE_Managers cte
  JOIN employees emp
    ON emp.manager = cte.employee
)
SELECT 
  emp.employee
, COUNT(DISTINCT mgr.employee) AS subordinates
FROM CTE_Employees emp
LEFT JOIN RCTE_Managers mgr 
  ON mgr.base_manager = emp.employee
GROUP BY emp.employee
ORDER BY COUNT(mgr.employee) DESC, emp.employee;


employee
subordinates


Robert
4

Julian
2

Lucy
1

John
0

Sophie
0


Warning: Null value is eliminated by an aggregate or other SET operation.