Surprised Mario 64 hasn't been mentioned yet. Back in the day, you can't imagine how incredible that big 3D Mario face was, add to that the fact that you could play around with it to make Mario do all kinds of crazy faces and 16 year old me was ecstatic!

Not so much the title screen, but I REALLY loved flipping through this menu:

https://www.youtube.com/watch?v=8OcLvrbQzsY

Some good ones here. I'm gonna cheat a bit. Just Cause 2. It didn't blow me away immediately, but it really grew on me. It got me pumped up to start the game and I would linger on it often, rarely tiring of it.

perfect dark was the first to pop into my head. seeing that n64 logo sinisterly morphing sent chills down my back!

Even though Wind Waker was my first Zelda game, it was Twilight Princess where I officially became a Zelda fan. And I knew it the moment I saw this.

Yeah, Detroit Become Human really stood out for me as well. It managed to produce an illusion of an actual AI that would greet me and say different things every time I booted up. And the change that happened in Chloe over time as you progressed in the game, very nice.
And TLOU as well, the peaceful and very low key title screen with the music and the curtains gently moving in the wind conveys heaps of atmosphere, even when you don't really know what you're getting into yet.

A lot of the Zeldas, and Super Metroid

I always liked Mario 64, given you could play with Mario's face.