Half-Life blew me away so much I watched the intro multiple times before starting the game. But that's a lot more than simply a title screen.
Nowadays I rather have title screens to go away fast. Actually that's always been the case as in the past the first 2 or 3 minutes of loading from the cassette tape was only to display the title / loading screen, then the actual game loading started.... But one stands out in my mind and has already been mentioned, Detroit become human. So mean she went away after finishing the game!