Clique no banner para conhecer e adquirir o meu treinamento de Bancos de Dados no Azure

SQL Server and Azure SQL - Challenge to group data using hierarchies

Post Views 413 views
Reading time 4 minutes

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

The biggest difficulty here is to group the data using hierarchies, so that the parent level (level 1) adds up the sale 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 tests, I will provide the script to create this same base:

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 the logic and quick to develop.

Final result
Option 1: New column with team sale

Option 2: New lines by selling the team

EDIT - Option 3: Solution sent by Cassio Junior

Result:

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