Games that have a long prologue or intro sequence before really taking off?
Persona 4 takes several hours for anything to happen. Unfortunately I put it down and gave up the first time. I tried again the next year and it became one of my favorite games ever.
Dragon Quest 7 takes a while too. They trimmed it down on the 3DS version.
What first came to mind reading the title for me is Earthbound. I love this game, but the grinding at the beginning is really annoying before Ness learns a healing ability.
But after reading the post, I will say there's definitely interesting story happening already at the beginning.
Ohh right of the top of my head, there's Pokémon Super Mystery Dungeon—I really enjoyed it and wound up playing until 100% completion (even if I don't think the game quite reached its full potential), but oh my god if I'm remembering correctly it takes like... six hours or so of solid playtime for the story to actually get going. And for those first six hours, you're pretty much stuck as an elementary school student basically LOL. I didn't think the early game was bad personally, but can I see how it lost a lot of people early on.
I feel like a lot of farming sims like the Rune Factory games and Stardew Valley can be pretty slow to start with too. Before you start unlocking features and things to do and gain more stamina/ways to refuel it, it's easy to wind up with little to nothing left you can do long before the in-game day is over. At least you can usually sleep whenever to skip straight to the next day!
Stardew, Portia, Sandrock, Coral Island
Basically any game where the objective is to grow your farm or the town since it takes a while to unlock everything and to have stable income to do all the other fun stuff like fighting and hangouts.