Three games that I think have truly amazing stories are Mother 3, Ghost Trick, and To the Moon.
I haven't played Mother 3, but I've seen others play it and even though I didn't understand the dialogue (because it's in Japanese and the game does not have an official release in the US to my knowledge) I still understood everything that was happening and... wow, what an emotional roller coaster.
Especially that Masked Man plot twist at the end.
Personally, a video game I played recently (last year) that I think had an amazing plot was Undertale. What the game doesn't have in overworld graphics, it makes up for in its gameplay and storytelling. I found it incredibly unique and original and I definitely recommend it even if you're not a fan of RPG games.
First off, the game knows its a game and there's one specific character that calls you out directly on every little thing you do in the game whether good or bad. This, along with the mythology of souls and determination, allowed for a lot of storytelling depending on how you acted in the game. If you were neutral and you killed some monsters you got a small part of a larger story, if you spared all monsters you got a major (and emotional :'() part of the story, and if you went out of your way to kill every monster then you got a creepy, more ambiguous side of the story. It's a lot deeper and complex than one would think at first, and you just start piecing it all together the more you talk to monsters, check random things, and perform different actions.
By the way, do not look up the Soulless True Pacifist ending. Trust me...