Would you consider SQL an abstraction over DynamoDB? ElasticSearch’s native query language? Apache Presto to query files? Mongo?

All of those can use SQL as a worse query language than their native counterparts.

You can also create very bad SQL if you don’t know the underlying engine. For instance if you try to write SQL for a columnar database like you would for a traditional database, you are in for a world of hurt.

You seem to be talking about databases who's native language is not SQL. In those cases, yes, SQL is definitely an abstraction. There's some layer that's transforming that into (say) Elastic query or Dynamo queries.

I would argue that when talking about writing SQL, most folks are talking about applications who's query planner talks some dialect of SQL. MS SQL Server, MariaDB, MySQL, Oracle, SQLite.

In those cases, it's not really an abstraction any more than writing assembly is an abstraction over (say) Intel's CPU microcode is.

The query planner takes your SQL and turns it into something else, sure, but you can't generally do that yourself. It's the lowest layer of abstraction that's reasonably available.