Everyone talks about the Turbo Tunnel in Battletoads, but for me, the sticking point was that @&$!? snake level. Extremely frustrating trial-and-error design where I would run out of lives before being able to memorize all the snake patterns.
Beating Revenge of Shinobi on Genesis was hard enough, but beating the final boss in time to save your girlfriend....that was REALLY hard. I actually did it legit once.
Also, Adventures of Bayou Billy on NES is one of those games that’s hard for all the wrong reasons, mainly cheap enemies and bad hit detection. I also beat this one legit once, but I had zero desire to ever play it again after that :p