Well, as an example of extreme non-hand holding, Dark Souls comes to mind. There’s a short introductory video that you can optionally skip, you’re dropped into a tutorial area with messages on the ground that are 100% optional to read, but the tutorial area is still fairly challenging but easy to beat quickly and get to the main game, and then you’re plopped down in the hub area with only a mild suggestion of which direction to head first.

I think a few bad examples of hand holding are lots of unskippable cutscenes right at the start of a game and a long unskippable and painful tutorial. Like when they explain what WASD movement is or camera panning. Those should 100% always be optional and skippable.

Also, when the hand holding takes all the fun out of the game, like a good example of Skyrim (or Oblivion) vs Morrowind; in Skyrim you get a quest, you follow the quest marker or look on your map, you do the thing, then you hand in the quest. In Morrowind you have to listen to every word people say, sometimes you have to refer to the text in your quest log if you get lost, you have to chart your own path towards where you think the quest objective might be, it’s way more involved and intricate and, well… Fun!

So basically hand holding is bad when it removes fun elements in favor of simplicity, and it’s bad when it involves an unskippable and tedious tutorial.

Personally, I’d say a game feels like “hand holding” when there’s no room for player agency. The key thing that sets video games aside as a medium is interactivity (as a two-way relationship) and player expression, and I think when a game is referred to as “hand holding”, it’s because there’s little-to-no room for players to express themselves - no obstacles to find creative solutions to, no way to play the game/section in different styles, etc.

I think the only real examples of “good” hand holding that spring to mind are optional tutorial sections where the player has specifically requested to be introduced to certain mechanics.

And there are absolutely games that aren’t handholdy enough, too - where players are just thrown in at the deep end without any explanation for what’s going on or how to play. Sometimes the lack of direction can be a good thing - exploration (of both a game’s world and its systems) can be exciting and rewarding, like discovering for yourself how to survive and advance in a survival game - but some games are so complex and overwhelming that players not being introduced to things more gradually can be really off-putting.

It’s difficult to say where I’d draw the line, though; I think it’s a somewhat personal thing and something that varies from game to game. Quite a few of the games I enjoy, such as Path Of Exile, are far too overwhelming for the “average person”. And a lot of “cinematic third-person action adventure” games (like the majority of Sony’s first-party titles) feel overly-restrictive to me, like I’m just being railroaded without really having to engage my brain much and without really being able to express myself through the gameplay. But the window of “handholdyness” that’s appropriate for me is wildly different to the average person, who does enjoy Sony games and probably doesn’t enjoy Path Of Exile.

I think this also somewhat ties into the idea of accessibility versus depth. The more handholdy a game is, the more accessible it is. But making a game accessible quite often involves removing the intricacies and complexities that result in mechanical/systematic depth. Is it possible for a game to have incredible depth, complexity and variety while also being intuitive and accessible for a brand nee player?