This is my experience too. I frequently require a single queue for some very specific task but it does not form part of a greater application.
My current work (data engineering) is such that I don't have a relational database instance that it would make sense to use instead. SQS is very straightforward and just works.
None of this is to say that I would necessarily advocate for a user-facing app to be _based_ on a queue-based architecture.
Almost every AWS architecture diagram will have a queue.
SQS is extremely stable, mature and cheap. Integrates with so many services and comes out of the box with good defaults.