Also, to be clear, there are highly useful features in many SQL versions that are not supported by others. We use them nonetheless
We use them too - but only when it solves a severe performance issue.
Because it's DRYer to have one code for all platforms than to maintain several versions of each query.
That would certainly be true if you need to support the same query on multiple platforms. That is not my experience. Also, keep in mind that this forum is SQL Server Central. It is not the best place to talk polyglot SQL, or ANSI SQL (which no one fully supports, I think) for that matter.
I just wanted to point out that "what is DRYer" may depend on other factors also.
Everyone developing SQL code should keep in mind that the management may come tomorrow with a decision like "we're supporting ORACLE also since the next release". And such decision may turn all of the "extra DRY" code into a nightmare.
Well, management may come in and say, "we're going pure Hadoop tomorrow. All you SQL guys, you're fired unless you can learn Java overnight!" I have never, ever developed code based on what "might" happen. Unless there's solid evidence to suggest some change in direction, I will most certainly not clutter my mind with it.
Therefore, my recommendation is:
Use CROSS APPLY only if you're sure you will never need to support another SQL engine.
The overhead of using CTE/subquery instead is negligible (at least in this use case).
Replacing a CROSS APPLY, as used in this article, with a CTE that does the same thing will add no overhead whatsoever. (Well, TBH there may be some difference in compile time, but that is not something I usually worry much about.)
My general recommendation that you exploit the features of the SQL engines you currently have (we run SQL Server, Oracle, DB2, MySql and probably others) and not spend much time worrying about "what if?"
FWIW I think it should be fairly straightforward to machine-translate in-row CROSS APPLY's to CTEs and vice versa. Once you parse the code (you could use the parser from any open source SQL engine), you can emit whatever code you like.