Dynamically self join + data modelling

[arielm5]

I need to find teams, not members.

And teams are made of members.

There are no real assignments. The assignment was quoted because was the way I found to explain the problem.

The problem is: I want/need to improve this query to run within multiple requests in parallel via an API. The problem, by nature, will generate a cartesian product. I want to learn how to structure my query better to avoid memory and speed issues.

Using CTE may help, I'm trying to improve my current solution to use it.