However long it takes them to tell their story. There is no ideal length in my opinion.
I like games that don't force you to spend 50-100 hours playing and can be beaten in 10-20 hours. Witcher 3 is a perfect example of this. People who want to play for the story can be done in 20-30 hours but those who want to do more can go as far up as 100-200. The sweet spot for me, personally, is 10-15 hours.
It all depends on the type of single player game. Is it action-adventure, an RPG, a platformer, hack n slash, etc..really hard question to answer with just a single number.
There's no hard-and-fast rule for any game. I loved every second of Horizon Zero Dawn's campaign, and was honestly sad when it was over at around the 20-hour mark. But it makes no sense to extend a story if you have nothing left to tell, story-wise. That being said, most every other game that has a campaign under 6 hours the first time through, and is a full-price game, would kind of feel like a rip-off, unless there's robust multiplayer options to compensate, or the story is absolutely incredible (the first Portal comes to mind). It really depends on how well-packed the game's campaign is.
It's got to feel right for the game. I finished RiME earlier this week, and at around five hours or so, it felt like a good length to deliver the story it needed to. Yet I am also happy with epics like The Witcher 3 or Mass Effect giving me stories that exceed the 20 hour mark easily. And of course Persona 5 is a cool 70+ hour game to get all the way to the end.
There's no such thing as a right length for a game, as it varies depending on the story being told. That said, I find it rare that a 20+ hour game actually earns its length completely. All too often games end up artificially lengthening themselves to the detriment of the overall experience.
Man, I know my answer will be controversial or unpopular. Why? Well, I'm an old fart with two kids, a wife, and this site to run each and every day. That means I have less time to game than most people think!
For me, the sweet spot would be 8-12 hours. Anything that crosses 15, and I automatically think, "I won't have the time nor patience to finish that!"
I want to keep tabs on the latest games, so I do play a lot, but don't necessarily finish them. I'll work through them bit by bit, but I just feel my energy be sapped out whenever I hear a game is 30-50 hours long.
Generally, I like games to take 10-20 hours to play through. Though, I'm perfectly happy to spend 1-2 hours playing a shorter title like Inside, which quickly gets to the point, or a 100-hour game like Persona 5, as long as it deserves the investment.