Employee Manager Query

Classic Employee Manager Problem
This is a classic interview question.

CREATE TABLE emp

(
empid int not null primary Key,
mgrid int,
empname char(10) 
)
go
alter table emp
add constraint fk_empxmgr Foreign key (mgrid) references emp(empid)
 
go
INSERT emp SELECT 3,NULL,'Sonu'
INSERT emp SELECT 2,3,'Mohan'
INSERT emp SELECT 1,2,'Viru'
INSERT emp SELECT 4,2,'Radhey'
INSERT emp SELECT 5,2,'Jai'

SELECT t1.empname [Employee], t2.empname [Manager]
FROM emp t1, emp t2
WHERE t1.mgrid = t2.empid
 
;WITH EmpsCTE (empid, mgrid, level)
AS
(
SELECT empid, mgrid, 0
FROM emp e
WHERE mgrid IS NULL
UNION ALL
SELECT e.empid, e.mgrid, m.level + 1
FROM emp e
INNER JOIN EmpsCTE AS m
ON e.mgrId = m.empid
)
SELECT e.empname employee,m.empname Manager, ct.level Level 
FROM EmpsCTE ct
inner JOIN emp e on e.empid = ct.empid
left JOIN emp m on m.empid = ct.mgrid


regards
DB