My favourite games have always been those which create an emotional reaction, or attachment to characters, as would any good book, movie, or TV show.
The best way to describe that is
the most recent episode of Westworld. (Warning: Spoilers)
It featured a character who has, thus far, been in a minor role and mostly in the background. The narrative and the story for that character, in that episode, is amongst the best fictional TV I think I've ever watched.
In many games, the main stories are often somewhat shallow and disappointing, although I thoroughly enjoyed walking in the boots of Geralt in the Witcher games, especially the 'Bloody Baron' story arc. That said, there are occasionally other games with moments that stand out and stay in mind.
One quite small side quest in Dying Light, briefly recovering some stuff for a kid, is one I remember most from the whole game. Simply because the narrative and how the characters were portrayed, was excellent and quite touching; in the midst of a game spent mostly moshing zombie heads with a lead pipe! I'm convinced that whoever wrote it, must have secretly done it while the other developers and script writers were out on a coffee break or something, haha.
As an adult capable of forming my own opinions and rarely offended by anything, one of the most frustrating aspect of game narratives is how they often pussyfoot around adult themed content, which inevitably ends up looking silly or out of place. I know age restriction and age classifications hardly help developers in that regard, when trying to maximise sales potential. However, if they're going to focus on adult themes, they should stick to their artistic vision and release the game as 'adult only', or whatever classification is required in different countries.
Let's be honest, there's far worse shown on the news each day, than there is in even the most explicit video games.