Tryna jump on that Rocket League bandwagon I see
Metroid, keeping it relevant
I'm glad System Shock 2 has been mentioned, since (disregarding the endgame from The Many and on) SS2 is a masters class in combining sustained tone, breadcrumbing through found artifacts, and witnessing events in real-time to create compelling, gameplay-focused narrative.
As a cautionary tale, though, it's worth noting that System Shock 2 was an
important game--not a commercially
successful one. In its first year on-market, it sold considerably less than 100k copies (one of them was mine!). While its spiritual successors (Bioshock) did much better commercially, they also abandoned SS2's heavy reliance on the narrative "iceberg effect" in favor of more explicit storytelling approaches.
I myself love the Prime games, but I understand why the more direct storytelling tactics of Other M might have seemed like a market necessity. It's a shame that a better story wasn't part of that product strategy. Not that I wouldn't have preferred another Prime title personally.