Sex scenes only add something to me if they are well done, no weird animations and/or porn style of filming, and most importantly if they come after a real connection moment. Cyberpunk 2077 isn't a perfect game by any standard really, but there is a romance with a character that builds over a long period of time. They start out not trusting you, to blaming you for something bad, finally starting to be friends, and eventually there is a day where the two of you spend together, having fun, and finally there is a sex scene at the end of the day. It's tastefully done (imo), doesn't just focus on the genitals, but seems more realistic. I'm no sex scene expert, but it was good for me, and didn't feel like it was designed to titillate, but rather show the connection these two people had made.

That romance/relationship really gave me a stronger connection to parts of the world/plot. Without any sort of relationship building (romance is part of this), you're simply walking along with random people, who might as well be hirelings for all the connection you make with them. Romance gives a stronger connection, so it's a positive thing imo.