I tend to actively avoid difficult games since I don't find them the least bit amusing. Probably the overall most difficult game I've played that I actually like is ResEvil4. It's got all manner of difficult segments. Out of all of em, the most challenging is a multi stage segment where you have to cover for Ashley as she runs all around the room where you can't follow her turning cranks while you have to keep Los Plagas off her back and off your face. Then you have to contend with wave after wave of the creeps coming at you with flails and crossbows from inaccessible ledges, blind corners and even through the freaking ceiling!!
Then there's the cage match with the blind armored giant with the freaking ripper claws roughly 1/3 as long as the cage is wide! He's scary quick in the direction of any noise you make so you can't run, is only vulnerable from the back so you have to stay behind him, and shooting him makes him turn and come swinging at you so as soon as you hit him you have to reposition without running for much more than a second!! Oh, and there are a bunch of flail and crossbow wielding cultists just outside the bars and remember: any gunfire draws the giant's attention so you really can't contend with them until you've taken him out which takes several shots even with the most powerful handguns - that's several turns around the cage through yard long ripper claws, spiked flails and a hail of crossbow bolts.
The cabin siege is difficult on a first playthrough since you're rather under-armed that early in the game. NG+ it's easy with a full arsenal. At least you can stow Ashley away where the villagers aren't likely to find her. You've got an endless succession of plague-ridden villagers coming at you from every direction (okay at least they don't come falling from the freaking ceiling this time!!) and a guy who is surprisingly self sufficient and even kinda helpful you have to keep alive, but a few stray shots too many and he'll FREAKING ONE SHOT YOU DEAD!! Which is a bit of an issue in such a confined space. Now, I am as adverse to being shot in the face as anyone else, accidentally or otherwise, but given the situation I don't see how killing me helps him any. jerk.
There's also a cavern where you have to take on a veritable swarm of these invisible giant flying bugs. This part is pretty straightforward actually but these things are quick, have odd movement and attack patterns and are difficult to see. There's also a lot of them and the cavern is pretty big and you have to traverse all up and down it to get out. If you're careful you can take on small groups of them at a time. But it takes a long time and small mistakes add up fast.
That's just the parts I remember and a whole thread could be written about ResEvil4's tricky bits.