Sometimes, finding a good workaround involves "thinking out of the box". Years ago, I would see people complain about the lack of subqueries in their version 4.0. Their query could easily be transformed into JOIN(s), but they were so used to subqueries in a competing product that they failed to think about JOIN. (As it turns out, MySQL's optimizer has been several years behind in development, so as recently as version 5.5 it was almost always better to use JOIN instead of subquery.)
I, too, have been irritated by not being able to use a TEMPORARY TABLE twice, and have yet to find a 'good' workaround. But I need it less than 1% of the time.
I don't think I have ever needed a FULL OUTER JOIN, and certainly not with more than two tables. Maybe I instinctively solve the problem with some workaround. What is your use case?
The MariaDB window functions seem to be reasonably clean and easy. Furthermore, the optimizer understands them and does a 'good' job (except in some extreme cases). Here are the docs:
https://mariadb.com/kb/en/mariadb/window-functions/
There was a talk at Percona Live in April, but the slides don't seem to be posted.
"Groupwise max" is a common task; it is trivially solved using windowing. There are many ways posted for solving it without windowing, but most are not efficient. I have tackled it here:
http://mysql.rjweb.org/doc.php/groupwise_max
At the bottom of that blog are links to may of the tough scaling/performance problems I have encountered, together with solutions that work in MySQL. The one on PARTITIONing bluntly says there are only 4 use cases for which PARTITIONs improve performance. Newcomers to MySQL thing that PARTITIONs will solve their performance problems; it probably won't.