TSQL - Mapping Parent/Child Hierarchy

Question

Sample Data:

DECLARE @Hierarchy TABLE
    (
        [ParentId] INT
      , [ChildId]  INT
    ) ;

INSERT INTO @Hierarchy
VALUES
    ( 1, 2 )
  , ( 1, 3 )
  , ( 2, 4 )
  , ( 3, 5 )
  , ( 4, 3 )
  , ( 4, 6 )
  , ( 5, 6 )
  , ( 7, 3 ) ;

Current Query:

; WITH CTE AS
    (
        SELECT  [ParentId]
              , [ChildId]
              , 1 AS [Level]
              , CONCAT ( CAST ( [ParentId] AS VARCHAR(MAX) ), '.', CAST ( [ChildId] AS VARCHAR(MAX) ) )  AS [Path]
        FROM    @Hierarchy
        UNION ALL
        SELECT  [C].[ParentId]
              , [T].[ChildId]
              , [C].[Level] + 1
              , CAST ( [C].[Path] + '.' + CAST([T].[ChildId] AS VARCHAR(MAX) ) AS VARCHAR(MAX) )
        FROM    CTE AS [C]
        JOIN    @Hierarchy AS [T]
        ON      [C].[ChildId] = [T].[ParentId]
    )
SELECT      *
FROM        CTE
ORDER BY    [ParentId]
          , [Level]
          , [ChildId] ;

Goal:

1. distinctly group levels of shared "path" together
2. find the shallowest and the deepest levels of the shared "path"

Expected Output:

NOTICE: the records with Orange highlight at the end are manually inserted to show what I'm expecting, but haven't figure out yet.

Group: Basically a "dense rank" of each "groups" of nodes that follow the same path. I think if you look at the values of Group in the above image and relate it to Level and Path field's data, it'll make more sense.

IsShallowest: 1st level (I can see that now that someone brought it up). Just need to figure out how to derive those missing (repeating) records

IsDeepest: max level within the group.

Think IsShallowest and IsDeepest is easy to figure out once "Group" logic is figured out and adding missing (repeated) records.

Answer 1

Please check this solution. It provide the requested solution except adding the extra row which more information is needed

;WITH CTE AS (
    SELECT  
        [ParentId]
        , [ChildId]
        , 1 AS [Level]
        , CONCAT ( CAST ( [ParentId] AS VARCHAR(MAX) ), '.', CAST ( [ChildId] AS VARCHAR(MAX) ) )  AS [Path]
        , MyGroup1 = ROW_NUMBER() OVER(ORDER BY [ParentId])
        , MyGroup2 = ROW_NUMBER() OVER(ORDER BY [ParentId])
    FROM Hierarchy
    UNION ALL
    SELECT  
        [C].[ParentId]
        , [T].[ChildId]
        , [C].[Level] + 1
        , CAST ( [C].[Path] + '.' + CAST([T].[ChildId] AS VARCHAR(MAX) ) AS VARCHAR(MAX) )
        , MyGroup1 = C.MyGroup1
        , MyGroup2 = [C].[MyGroup1] + ROW_NUMBER() OVER(ORDER BY [T].[ParentId]) - 1
    FROM CTE AS [C]
    JOIN Hierarchy AS [T] ON [C].[ChildId] = [T].[ParentId]
)
, MyCTE2 as (
    SELECT
        [ParentId]
        , [ChildId]
        , [Level]
        , [Path]
        -- un-comment bellow 2 rows to see the logic
        --, MyGroup1
        --, MyGroup2
        , MyGroup = DENSE_RANK() OVER (ORDER BY MyGroup1, MyGroup2)
    FROM CTE
),
MyCTE3 as (
    SELECT
        [ParentId]
        , [ChildId]
        , [Level]
        , [Path]
        , MyGroup 
        , shallowest = ROW_NUMBER() OVER(PARTITION BY MyGroup ORDER BY [Path]) 
        , deepest = ROW_NUMBER() OVER(PARTITION BY MyGroup ORDER BY [Path] DESC) 
    FROM MyCTE2
)
SELECT
    [ParentId]
    , [ChildId]
    , [Level]
    , [Path]
    , MyGroup 
    , ISshallowest = CASE WHEN shallowest = 1 then 1 else 0 END
    , Isdeepest = CASE WHEN deepest = 1 then 1 else 0 END
FROM MyCTE3
ORDER BY
    --[Path]
    [ParentId]
    , [Level]
    , [ChildId];