I'll exclude series/games where I just like the story.
The Last Blade/Bakumatsu Romance 1 and 2 had quality, minimal tales told flawlessly through short cutscenes, level design, and overall tone. That's very important to me in a "highbrow" single-player mode.
For content, Virtua Fighter 4's and 5's postmodern "arcade traveler" Kumite/Quest modes are constantly thrilling, as streamlined as they need to be since they lack stories, offer an absurd level of content and customization, and challenge you in ways that help you grow as a player. I know that Mortal Kombat 9's single-player gets praised for most of the same reasons, but the mode didn't really make me think or develop.