I could probably give you a better answer if I thought about it a lot more, but right of the top of my head, I think some of my favourite video game plots follow a similar formula to some of my favourite film plot structures. One film structure I love is the pursuit of an "object of desire" so to speak. Those films where everybody is going bonkers trying to get their hands on one thing. Think about the ransom money in The Big Lebowski, the diamonds in Reservoir Dogs, or the opal in Uncut Gems. I absolutely love those simple self contained plots where antics ensue between relatively ordinary people, but the stakes become surprisingly high.
Two of my favourite game plots are:
Vampire The Masquerade: Bloodlines
This is an RPG so you have a glorious amount of agency over your character build which ties wonderfully into the vampire lore in the game world, but the big draw for me is less about my own character and more about the actual plotline this game puts you through. The tone of this game is a dramatic neo-noir with vampires in a seedy depiction of Los Angeles. There is also a healthy dose of comedy sprinkled throughout.
I love it because of how simple yet thrilling the plot is. The premise is that you are a human turned vampire and are spared an execution from a powerful Vampire Prince of a secret society clan called the Camirila. You're told to do a few odd jobs for the Price which leads you to cross paths with all of these different vampire clans hidden in plain sight in LA. The goal of the game is to find and retrieve this ancient and powerful sarcophagus which is said to have supernatural powers. You, the Prince, and all of the different vampire factions and local gangs are all in this hard boiled race to open the sarcophagus.
It's so simple yet so effective and it leads to many twists, turns, betrayals, and desperate moments. I think this game might have my favourite overarching story in gaming.
Fallout: New Vegas
Read everything I said about Vampire The Masquerade and apply it to the different factions fighting over Hoover Dam in "New Vegas", Nevada. I could go into more detail but all of the same reasons I love Vampire apply to this game. It's this comedic yet often dramatic storytelling where everybody is trying to get one over on the next person. The difference here is that you have more player agency to affect the plot which is nice. You can outright kill heavy hitters in the early game and the story will adapt later on which is unique.
