To me those nested sub sub sub SQL queries come from a similar place as beginner coders who tend to make nested IF statements - a lack of experience with the language.

For very complicated stuff, SQL does become very hard to read compared to e.g. tidyverse + targets in R.