I guess my first example would be HoMM3. I got my hands on a demo back when it released originally, and I thought it was incredible. Of course, the computer wiped the floor with me each time :P

As for right now, that game is Dwarf Fortress. I'm trying to make some headway into it, and so far I am miserably failing at each turn. Yet weirdly, it's incredibly fun at the same time. The interface kind of sucks though :P

edit: Oh yeah, and starcraft. I thought I was pretty good at it until I tried playing against my friends :P And Red Alert for that matter, which is when I learned for the first time that skirmish tactics are less than useless against even an okayish human :P