I thought the general consensus these days was that Ikari Warriors, Mutant Virus, Starship Hector, Q*bert, Bump 'n' Jump, and The Last Starfighter were pretty much the top tier of NES difficulty. (Maybe Championship Pool and To the Earth too.)
I was watching SomeCallmeJohnny playing Dragon Power(Dragon Ball: Shenlong no Nazo) the other day.
It was apparently so bullshit difficult that he couldn't even finish it, despite having the patience and skill to do a complete run of Ghost n' Goblins.
Off the top of my head there's also Ghostbusters which AVGN played
The big problem with Dragon Power is that, since your health ticks down steadily, your survival often depends on item drops (from non-respawning enemies) that are driven by a RNG. If the game decides to screw you, there's nothing you can do about it. I beat it once, and my successful run owed more to getting favorable item drops than anything else.
The stairs in Ghostbusters would be insanely difficult if it weren't for the bug that makes you essentially invincible, which is very easy to trigger accidentally since it arises out of normal gameplay. With that, it's not especially hard to beat; without it, and without turbo...
Adventures Of Captain Comic, Cobra Triangle, Ghost N Goblins are some of the hardest games I've played for the NES. Cobra Triangle is one of my favorite, and I'd love to see a remake for current gen consoles.
Odd, I never thought of Captain Comic as top-tier hard, though of course it's tricky. I like that game a lot and have beaten it a couple times. Ghosts 'n Goblins is aggravatingly tough but has unlimited continues, so it's beatable with enough persistence. Cobra Triangle is quite difficult, IMHO -- several Rare games on the NES are (as Jorpho pointed out) -- and I've never beaten it.
I have no idea why people think Double Dragon III is so hard. The only part of that game I find challenging at all is the final boss; the first four levels are downright easy. And you are given a continue at Level 4, BTW.