I'm not a prude but I've never understood sex scenes in movies. I don't want to watch that with my friends and family, nor do I want to watch that with strangers in a theatre. If I want to watch people fuck, I can just watch porn, thanks.
Sex scenes are going out of favour with the current generation, but there's value in having a platform for showing sex in a "normal" contexts as part of a whole charachter. Otherwise the only portrayal of sex is left to porn.
I just think they're weird and unnecessary. I've never seen a sex scene that was important for the plot. They seem to just be added because they're sexy and I don't need to be turned on when I'm hanging out with friends and family, so what's the point?
I think there are several exceptions to this. Sex scenes in general follow “show, don’t tell:” having a character simply do X is usually preferable to having a character say “I did X.”
Some examples off the top of my head:
The Terminator - It shows how the main character becomes pregnant with minimal exposition
Shoot ‘Em Up - It highlights the absurdity of the film, equates sex with gratuitous violence, and tells you about the characters
Sausage Party - it is an illustration of The Absurd in the wake of an existential crisis
Team America: World Police - It functions as shock humor that also satirizes other gratuitous sex scenes
Irreversible- There is simply no amount of dialogue that could illustrate the amount of horror and violence the scene conveys and bring about the proper emotional response from the audience
Jackie Brown - It shows how pathetic the main characters are
Tomcats - It creates a comedic moment when the timid librarian turns out to be a dominatrix
Trainspotting - The film compares the high from heroin to an orgasm, and the sex scene extends that metaphor and helps the audience empathize with the main character’s struggle
Requiem for a Dream - Shows how Jennifer Connelly’s character has hit rock bottom
interestingly, they said this about "The Kiss," an 18-second long early cinema piece (and the first cinema kiss) in which a man combs his mustache and then briefly pecks a woman on the lips.
A contemporary Critic wrote: "beastly enough in life size on the stage, but magnified to gargantuan proportions and repeated three times over, it is absolutely disgusting."