We surely have encountered data which was structure in hierarchical mode. One example is storing employees as managers and their direct reports.

The usage sample are already well explained in Microsoft docs.

However the most important thing to remember is the way the recursion takes place.

You need to compose the Common Table Expression (CTE) from anchor member(s) and recursive member(s), at least one from each type, and they need to be combined with a UNION ALL.

WITH DirectReports(ManagerID, EmployeeID, Title, EmployeeLevel) AS   
(  
    --anchor member
    SELECT ManagerID, EmployeeID, Title, 0 AS EmployeeLevel  
    FROM dbo.MyEmployees   
    WHERE ManagerID IS NULL  
    UNION ALL  
    --recursive member
    SELECT e.ManagerID, e.EmployeeID, e.Title, EmployeeLevel + 1  
    FROM dbo.MyEmployees AS e  
        INNER JOIN DirectReports AS d  
        ON e.ManagerID = d.EmployeeID   
)  
SELECT ManagerID, EmployeeID, Title, EmployeeLevel   
FROM DirectReports  
ORDER BY ManagerID;

The actually recursion process is well explained here.

Hope it is interesting.