When I was young there were two types of games I tended to enjoy: single-player games (e.g. Nethack, Half-life, Starcraft, & others with a good story and gameplay, or just deep gameplay) and LAN party games (e.g. Unreal Tournament, Counter Strike, Total Annihilation, Quake II, and similar). LAN party games were more fun at LAN parties than online, and not just because server browsers all kind of sucked. Playing along with other people you can see in the same room is a very different experience from playing along with other people you've never met, can't see, and will never encounter again.
These days my friends are scattered across the country, with jobs & families, and so LAN parties are basically dead. And many new games don't even support LAN play, instead they tend to be optimized for online play with some sort of ranking system.
That leaves single-player games. And really good single-player games are rare, just like really good anything is rare. I find a lot of story-driven singleplayer games have good stories, but crap gameplay, so it's frustrating to try to complete the story. If the story is good enough & the gameplay bad enough I'll just cheat & treat the whole thing more like a book or movie instead of a game, but for a lot of games I just don't bother even with that.
But occasionally a game grabs me. The story is great, and the gameplay is at least good enough, or it's just really good gameplay that stays engaging for a long time (e.g. Slay the Spire). These are few & far between, because making really good games is very difficult.
As I age my tolerance for mediocrity decreases, partly because I already own a whole bunch of still-engaging games I can always play. So I agree with your points. The really great games are rare, far rarer than best-selling games.