Hey guys!
In this post I would like to share with you an SQL challenge I received involving hierarchies:

The biggest difficulty here is grouping the data using hierarchies, so that the parent level (level 1) adds up the sales value of all children (level 2+). The level below (level 2) must add up the sales of all their children (level 3+) and so on.

And of course I accepted the challenge and tried to solve it, right?

Creating the test base

To facilitate your testing, I will provide the script to create this same base:

-------------------------------------
-- Cria a tabela com dados de teste
-------------------------------------

DROP TABLE IF EXISTS #Hierarquia_Vendas
CREATE TABLE #Hierarquia_Vendas (
    Cd_Vendedor INT NOT NULL,
    Cd_Superior INT NULL
)

INSERT INTO #Hierarquia_Vendas
VALUES 
    ( 301, NULL ),
    ( 209, 301 ),
    ( 576, 301 ),
    ( 111, 209 ),
    ( 112, 209 ),
    ( 123, 576 ),
    ( 444, 576 )


DROP TABLE IF EXISTS #Vendas
CREATE TABLE #Vendas (
    Cd_Vendedor INT NOT NULL,
    Venda NUMERIC(18, 2) NOT NULL
)

INSERT INTO #Vendas
VALUES
    (301, 25),
    (209, 30),
    (111, 80),
    (112, 70),
    (576, 50),
    (123, 100),
    (444, 120)

Data preview:

How I solved this challenge

To solve this challenge, I used recursive CTE, WHILE and subquery, in order to present a solution that is simple to understand and quick to develop.

--------------------------------------------------------
-- Cria a tabela com dados de teste
--------------------------------------------------------

DROP TABLE IF EXISTS #Hierarquia_Vendas
CREATE TABLE #Hierarquia_Vendas (
    Cd_Vendedor INT NOT NULL,
    Cd_Superior INT NULL
)

INSERT INTO #Hierarquia_Vendas
VALUES 
    ( 301, NULL ),
    ( 209, 301 ),
    ( 576, 301 ),
    ( 111, 209 ),
    ( 112, 209 ),
    ( 123, 576 ),
    ( 444, 576 )


DROP TABLE IF EXISTS #Vendas
CREATE TABLE #Vendas (
    Cd_Vendedor INT NOT NULL,
    Venda NUMERIC(18, 2) NOT NULL
)

INSERT INTO #Vendas
VALUES
    (301, 25),
    (209, 30),
    (111, 80),
    (112, 70),
    (576, 50),
    (123, 100),
    (444, 120)


--------------------------------------------------------
-- Cria o nível de profundidade e a string de hierarquia
--------------------------------------------------------

DROP TABLE IF EXISTS #Base;

-- Vou utilizar essa CTE resursiva para gerar o nível de profundidade e a string da hierarquia
;WITH cte AS (

    -- Nivel 1
    SELECT 
        1 AS Nivel,
        CONVERT(VARCHAR(MAX), CONCAT('', [Cd_Vendedor])) AS Hierarquia,
        [Cd_Superior],
        [Cd_Vendedor]
    FROM
        [#Hierarquia_Vendas]
    WHERE
        [Cd_Superior] IS NULL

    UNION ALL

    -- Nivel 2->N
    SELECT 
        B.[Nivel] + 1 AS Nivel,
        CONVERT(VARCHAR(MAX), CONCAT(B.[Hierarquia], '-', A.[Cd_Vendedor])) AS Hierarquia,
        A.[Cd_Superior],
        A.[Cd_Vendedor]
    FROM
        [#Hierarquia_Vendas] A
        JOIN [cte] B ON [A].[Cd_Superior] = [B].[Cd_Vendedor]
        
)
SELECT
    A.[Nivel],
    A.[Cd_Vendedor],
    A.[Cd_Superior],
    A.[Hierarquia],
    ISNULL([B].[Venda], 0) AS Venda,
    
    -- Coluna que vai guardar a venda da equipe
    CAST(NULL AS NUMERIC(18, 2)) AS Venda_Equipe,
    
    -- Coluna utilizada para ordenar os resultados
    ROW_NUMBER() OVER(ORDER BY ISNULL(A.[Hierarquia], -1)) AS Ordem
INTO
    #Base
FROM
    cte AS A
    LEFT JOIN [#Vendas] B ON [A].[Cd_Vendedor] = [B].[Cd_Vendedor]
    


--------------------------------------------------------
-- Calcula a venda por equipe de forma agregada
--------------------------------------------------------

DECLARE
    @MenorNivel INT = 1,
    @NivelAtual INT = (SELECT MAX(Nivel) FROM [#Base])

-- Calcula do maior do nível para o menor
WHILE(@NivelAtual >= @MenorNivel)
BEGIN
    

    -- Atualiza a coluna "Venda_Equipe" com a soma das vendas do nível acima para o mesmo superior
    UPDATE A
    SET
        A.[Venda_Equipe] = ISNULL(A.[Venda], 0) + ISNULL(B.[Venda_Equipe], 0)
    FROM
        [#Base] A
        LEFT JOIN (
            SELECT 
                ISNULL([Cd_Superior], -1) AS Cd_Superior,
                SUM(ISNULL([Venda_Equipe], 0)) AS Venda_Equipe
            FROM
                [#Base]
            WHERE
                [Nivel] = @NivelAtual + 1
            GROUP BY
                ISNULL([Cd_Superior], -1)
        ) B ON A.[Cd_Vendedor] = B.[Cd_Superior]
    WHERE
        A.[Nivel] = @NivelAtual


    SET @NivelAtual -= 1 -- Vai reduzindo o nível até acabar
    

END


--------------------------------------------------------
-- Executa a consulta final
--------------------------------------------------------

-- Opção 1: Nova coluna com a venda da equipe
SELECT * FROM [#Base]


-- Opção 2: Novas linhas com a venda da equipe
;WITH cteFinal 
AS (
    SELECT
        [Nivel],
        [Hierarquia],
        [Venda],
        'Venda Direta' AS Agrupador,
        [Ordem]
    FROM 
        [#Base]

    UNION ALL

    SELECT 
        [Nivel],
        [Hierarquia],
        [Venda_Equipe],
        'Total Venda Equipe' AS Agrupador,
        [Ordem]
    FROM 
        [#Base]
)
SELECT 
    [Nivel],
    [Hierarquia],
    [Venda],
    [Agrupador]
FROM
    [cteFinal]
ORDER BY
    [Ordem],
    IIF(Agrupador = 'Venda Direta', 1, 2)

Final Result
Option 1: New column with the sale of the team

Option 2: New lines with the sale of the team

EDIT – Option 3: Solution sent by Cassio Junior

;WITH ListHierarquia_Vendas AS
(
	SELECT
        [a].[Cd_Superior],
        [a].[Cd_Vendedor],
        CONVERT(DEC(10, 2), [Vendas].[Venda]) AS [Venda],
        1 AS [LevelId]
    FROM
        [#Hierarquia_Vendas] AS [a]
        INNER JOIN [#Vendas] AS [Vendas] ON [a].[Cd_Vendedor] = [Vendas].[Cd_Vendedor]
    WHERE
        [a].[Cd_Superior] IS NULL

    UNION ALL

    SELECT
        [Hierarquia_Vendas].[Cd_Superior],
        [Hierarquia_Vendas].[Cd_Vendedor],
        CONVERT(DEC(10, 2), [Vendas].[Venda]) AS [Venda],
        [ListHierarquia_Vendas].[LevelId] + 1 AS [LevelId]
    FROM
        [ListHierarquia_Vendas] AS [ListHierarquia_Vendas]
        INNER JOIN (
            [#Hierarquia_Vendas] AS [Hierarquia_Vendas]
            INNER JOIN [#Vendas] AS [Vendas] ON [Hierarquia_Vendas].[Cd_Vendedor] = [Vendas].[Cd_Vendedor]
        ) ON [ListHierarquia_Vendas].[Cd_Vendedor] = [Hierarquia_Vendas].[Cd_Superior]
),
ValoresVendasFilhos AS
(
	SELECT 
	    Cd_Superior,
            [ListHierarquia_Vendas].[Venda]
	FROM
            ListHierarquia_Vendas
	WHERE
            [ListHierarquia_Vendas].[LevelId] > 1
),
SomaVendaPai AS
(
    SELECT 
        [b].[Cd_Superior],
        [b].[Cd_Vendedor],
        [b].[Venda],
        [b].[LevelId],
        IIF([b].[LevelId] = 1, SUM([b].[Venda]) OVER (PARTITION BY NULL), NULL) AS [SumLevel1]
    FROM
        [ListHierarquia_Vendas] AS [b]
)
,SomaVendasFilhos as
(
    SELECT
        [Cd_Superior],
        SUM([ValoresVendasFilhos].[Venda]) AS [Venda]
    FROM
        [ValoresVendasFilhos]
    GROUP BY
        [Cd_Superior]
)
SELECT
    [SomaVendaPai].[Cd_Vendedor],
    [SomaVendaPai].[Cd_Superior],
    ISNULL(
        IIF([SomaVendaPai].[LevelId] = 1,
            [SomaVendaPai].[SumLevel1],
            ([SomaVendaPai].[Venda] + [SomaVendasFilhos].[Venda])),
        [SomaVendaPai].[Venda]
    ) AS [Venda]
FROM
    [SomaVendaPai] AS [SomaVendaPai]
    LEFT JOIN [SomaVendasFilhos] AS [SomaVendasFilhos] ON [SomaVendaPai].[Cd_Vendedor] = [SomaVendasFilhos].[Cd_Superior];

Result:

And you? How would you solve this challenge? Could you find a simpler or faster query than the one presented in the article?
Post your solution here in the comments.
I'm looking forward to seeing what you guys did 🙂