I would absolutely say the most stunning moments are the pride-before-the-fall moments.
You know, when you've been absolutely kicking ass, and everything coming through your headset is grumbling acknowledgement of your superiority. You're picking people off like ants under your feet. If anything, you're getting complacent, and bored.
All of a sudden, you're shot in the back. You spin round, absolutely amazed, and get a knife between the ribs. As the camera whirls away, you realise this was the quiet kid, the one who wasn't grumbling as he blindly ran into your fire. No, this kid watched very patiently, and over the course of a good 20 minutes edged around the entire map, watching for your blind spot and taking advantage of your moments of sheer trigger-happiness to infiltrate your base. And he had the steadiness of hand to creep up on you and not take a single shot until he was absolutely sure he had your number.
Very humbling. Despite one player's skill and prowess, he can still be defeated by someone of stronger endurance, cunning and will. I felt like Sauron.
Also humbling for the amount of cheering and jeering that immediately followed. Reminded me that dictators are never loved, and that the no.1 spot is a lonely place. Since then I've mostly played team capture the flag.