It seems that you need to get better. There are plenty of valid complaints against SQL, but your problems seem to be all due to lack of familiarity.

No variables, no functions; Oh but you can do a CTE

Yeah, CTEs are more expressive than variables. And as somebody pointed, every database out there supports functions, you may want to look how they work.

UNION ALL, UNION ALL, UNION ALL… “There’s got to be a better way, surely…”

What do you mean by a "better way"? Union all is a perfectly valid operation.

And then you try put a MAX in a where and it won’t let you because you gotta pull all the maxes out in their own query, make a table, join them in, and use them like a filter…

Window functions exist.