This is kind of hard to answer, because with a lot of games I've only found them hard because I've not known how to play them properly. With most RPGs it's just a case of "grind until you're strong enough to win" and with a lot of action games, you just need to learn attack patterns of enemies, or placement of hazards, and time your movements accordingly. Most puzzles are tricky the first time too, but once you get the solution they're straightforward. I've had a lot of unpleasant, painful experiences, but they've always been only the first time.
In terms of well-designed, balanced gameplay, I think the hardest game I've played so far is probably Ikaruga. I am not particularly good at bullet hell shooters, and even when I revisited it on the Switch when it re-released I went through a lot of pain to finish it despite how short it is. Some of the songs in Project Diva songs are also borderline impossible on higher difficulty settings. How people manage to clear songs like Negaposi*Continue and Sadistic Music Factory above Normal difficulty is beyond me. I have good muscle memory and reflexes, but I think these are more than a little beyond my skill level!
As for games that are hard because of poor game design...Wo Long: Fallen Dynasty comes to mind, although that is probably recency bias. Because this is a thing with Koei Tecmo Soulslike games: they completely miss the point of what makes those games challenging and just amp it up to unfair levels at specific boss choke points for the sake of it. Lu Bu, in that game's case. Everything before, and much of what came after, was significantly easier. It was like going up against Flamelurker in Demon's Souls all over again, right down to the fire theme. Lu Bu was disgustingly unfair.
Part of me wants to say the Xbox 360 version of The Last Remnant though, on the basis of the Curse status effect, which has a 50% chance of killing you every turn...and if your party leader dies, that's the end. A lot of enemies utilise Curse too, and it's incurable. There are a lot of nasty, unfair status effects in that game, but Curse is probably the worst I have ever seen in my thirty-odd years of playing video games. The PC version is slightly more forgiving because if your party leader dies you don't lose the entire team, but it's still absolutely brutal. Dragon Quest II is actually in a similar boat for its aggravating roulette of status effects that can see you sleeplocked and murdered regardless of your level, right up to the end of the game. The random encounter rate certainly doesn't help matters.