Depends on the genre for me.
Stealth games I always play on hardest, because I strive to never be seen anyway. Playing Dishonored for the first time right now and chose the hardest difficulty out of 4... it's still pretty easy, honestly. I am good at stealth games because I am extremely patient.
Shooters I am pretty good at, but the hardest levels are often frustrating. Also many games are balanced for some measure of auto-aim on consoles, so the faster mouse aiming doesn't really give one the advantage you might think it does. So I play on normal most of the time, or the 3rd level if there are 4.
RPGs rarely have difficulty levels but when they do, like Skyrim, I play on normal. I am mostly playing RPGs for exploration and story, not combat. One exception would be the Dragon Age games, which needs to be played on hard or so to offer the same tactical challenge of Bioware's older games.
Overall I do think games should provide a challenge but not be frustrating. It can be a fine line to walk sometimes.
wpegg: Yes that line exists, it's defined in Assassins Creed 3. It's called beat the control system cos the devs are shit. Hard games that you can be skillful at are fine, games that are hard because of an esoteric control system are not.
The AC controls just suck, difficulty level be damned.