I'd take ISO/IEC 9075 as an official specification: https://webstore.iec.ch/publication/59685
> and isn't as powerful as I'd always want it to be
Do you have examples? There is a lot in SQL that is not commonly known—if you let me know what you are thinking of, I might be able to show you an adequate SQL feature.
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.
> > I'd take ISO/IEC 9075 as an official specification
> Fair enough, but I'd argue it's not a de facto standard...
It may be useful to understand the distinction between official (de jure) and common usage (de facto).