>I'd take ISO/IEC 9075 as an official specification

Fair enough, but I'd argue it's not a de facto standard because the vast majority of implementers don't follow it. Case in point: SQL is rarely portable across databases, even if you're e.g. moving from postgres to a postgres-like system like bigquery or redshift.

>Do you have examples?

For me (as a machine learning + data engineer) it often comes up around aggregating with window functions. Things that would take a couple lines in R or Python or Scala can take dozen of extra lines with superfluous CTEs.