
left join an effective satellite without duplicating rows (use a cte)
when an entity link to another hub is optional, a naive left join that pulls in an effective satellite in the same from list can explode or blur grain. i isolate the current effective row in a cte and left join that instead.
