Well, at the heart of it, a sex scene is just another scene, right? It can be written as an emotionally charged character moment, or an action scene, or whatever. Whether or not it’s two characters getting it on shouldn’t have any bearing on whether or not the scene works.
I mean, if the author relies too much on sex being titillating on its own, chances are it is going to be boring, because there are only so many ways you can describe the hydraulics (to use @jbaillier‘s favorite turn of phrase). But if the sex scene is crafted with the same care and effort as the rest of the story, and actually plays a part in the whole, why should that be any different than, say, a big fight scene or a major reveal?
Honestly, I’m curious. I do realize there’s a whole lot of subjective (and possibly subconscious) thought processes and attitudes and preferences that goes into this, because sex ooh aah. I suppose what I’m rambling on about here is personal preferences vs. the quality of a scene. The subjective vs. the objective. There has to be people who skip fight scenes, too, no matter how well they are written.
But as to the OP’s point: no, a story doesn’t need sex to be good. It’s just another tool in the writer’s toolbox. It may be the perfect fit for some stories, but it’s not going to work for everything.