Mine is from Streets of Rage Remake V5.1, way of the warrior, Golden Rage and Streets of Rage 1 and 2 collection. Everything besides way of the warrior, is the marvelous, lovable work of my favorite modder ever, (name blank)!

Desert Combat for BF1942

I don't remember the name, but I once played an adventure from neverintervault where you met "the flame" (fire elemental), the thing (earth elemental) and some others. It played in a town and was hilarious.

Of course all the Diablo2 mods at Frozenkeep and before that we played Counterstrike as Half Life2 mod.

All the great stuff done on the Nexus for Skyrim, Witcher1/2/3 and all the others. ARMA mods at Armaholic...

p.s. There are so many beautiful player made modifications to so many games. Even if people are just testing something out, it may give others ideas to a great thing.

Gekokujo, a mod for Mount & Blade: Warband. Although I'm not a fan of the looks of the women in that mod, japanese women, I can't do anything about it since that's how those women mostly look like in Japan.

What I like about the mod are the variety of armors, weapons, and most importantly, the banners. The mod also adds different sets of new commands that you can give to your troops on the battlefield.

Fall from heaven and the mod mod Fall further for CIv 4.
Fantasy setting civ game. Lots of races and units. Very fun.

The Stanley Parable.

Hot Coffee

X-TM for X3 Reunion. It was so good that egosoft worked together with the modders to create X3:TC and AP

I remember being particularly fond of Jailbreak for the original UT.

More recently, Brutal Doom.

Admittedly, I'm not much of a mod guy.

I was very impressed by the quality of Paranoia for Half-Life. I also played a lot of Wanted!, though I wouldn't consider that one the best I've played.

Nehrim

Tamriel Rebuilt (WIP)

Most of those unofficial patches for older RPG-s don't count as mods? Then I'm not going to list them here (A LOT OF THEM EXIST)

Batman DOOM
Batman vs. Bane
Batman: No Man's Land
BlackMesa
Brutal DOOM
Portal Thinking With Time Machine
S.T.A.L.K.E.R. Lost Alpha
Silent Hill: Alchemilla
X-Men Ravage of Apocalypse
....

I'll go more Retro and say the Aliens: Total conversion for Doom. Recreated the main story elements from the second Aliens film complete with music, voice clips, and even the Aliens camouflaged in the walls in the early part of the game. Even had complete weapons replacements such as the pulse Rifle, Smart Guns, and the Power Loader.

Bonus was that it messed up the cheat codes so that if you tried to use them it would actually handicap you (such as making the power loader malfunction so it couldn't attack and take forever to swap weapons leaving you completely vulnerable.

Until Baldur's Gate Enhanced Edition was made, it was Tutu for Baldur's Gate, allowing me to play Baldurs Gate 1 in the Baldurs Gate 2 engine.