Tifa's limit break on the piano in FF7 was pretty ridiculous, even though if you play the tune the right way you realize that it's actually the main theme of FF7.

Most puzzles in the adventure games I play (such as the Monkey Island series) can be almost impossible to figure out without a walkthrough.

I know this has happened to me on other games, but I can't remember them at the moment.