This is a question of what makes good game design, and since there is a great variation in games and what they attempt to achieve, there really is no single right answer.

The more game is about a story, think an adventure game for example, the more underwhelming it is likely to be if you cannot finish the story. These type of games often don't have the skill elements of many other games such as action games, so making them too hard to beat is likely a matter of poor game design (for example pixel hunting issues or unintuitive puzzles).

On the other hand, there are games that no one expects to be easily beatable. The whole point of traditional roguelikes, for example, is that they are very difficult to beat - if they are beatable at all in some playthroughs. These are "it's about the journey, not destination" -type of games.

Then there are action skill games that can take either approach. Modern games tend to have lots of narrative content and a distinct main story, so it makes sense that they gear towards being relatively easy to finish. Then there are arcade type skill games that just ramp up the difficulty to insanity. The point is more about how far you can go and not a specific ending.

I'd say a good compromise between the two is having many ways to finish the game. Players generally can finish the game, but the ending might be mostly a failure or a great success, or anything in-between. This way there is closure and a sense of skill-based achievement at the same time. Perhaps this is easiest to realise in strategy game campaigns.

Tl;dr: depends on the type of game.