To be fair, lateral joins (cross/outer apply in mssql) can help with name aliasing and table functions give sql some reusability. I think the main pain points for sql are pivots and window functions.
A lot of the time you just want to transpose your result, but you have to choose an aggregate and handle null cases to force pivot to work the way you want it.
And a lot of the time you want to aggregate a window but keep the ids of the row so you avoid the having keyword altogether and go for row_number and dense_rank to get your aggregate results.
If I were to write a query language, I would discard group by and having and make it easier to apply transpose and window functions.
A lot of the time you just want to transpose your result, but you have to choose an aggregate and handle null cases to force pivot to work the way you want it.
And a lot of the time you want to aggregate a window but keep the ids of the row so you avoid the having keyword altogether and go for row_number and dense_rank to get your aggregate results.
If I were to write a query language, I would discard group by and having and make it easier to apply transpose and window functions.