If I remember correctly, that was the original idea of AWS, to offer their free capacity to paying customers.

Do you think that AWS in particular has this problem or Azure and GCP as well? I have mainly worked with DWHs in Snowflake, where you can adjust the compute capacity within seconds. So you pay almost exactly for the capacity you really need.
Not having to optimize queries is a good selling point for cloud-based databases, too.

It is certainly still cheaper than self-hosted on-premises infrastructure.

Do you mean that it’s still the case that more resources are allocated than actually used or that the code does not need to be optimized anymore due to elastic compute?

This is the right way