I’d have to go with either getting the Sheep Attack in Super Mario RPG, or finding Grate Guy’s casino in SMRPG. I had the player’s guide, thankfully, but how are you supposed to know that you get the Lamb’s Lure by using a Mystery Egg 10 times as Toadstool equipped with the B’tub Ring, then get Sheep Attack by using Lamb’s Lure 48 times? For the casino, you have to defeat a Chomp Chomp in a certain area and jump three times to make an invisible platform appear.
Lufia II’s ‘World’s Most Difficult Trick’ is up there too, which is a sliding block puzzle with 116 steps to complete.
And the goat puzzle from Broken Sword is so infamous it has its own Wikipedia page. https://en.wikipedia.org/wiki/The_Goat_Puzzle
The Witness has some stupid ass hard puzzles but not in a good way. It’s not wisely hard, it’s disgustingly hard. Some of the puzzles are bot disgustingly hard and time waster.
If you’re looking for wisely hard, challenging but also smart puzzles then I can offer some The Talos Principle puzzles. Regular ones are not that challenging but more rare/hidden ones are really really well designed and gives great satisfaction.