The most impressive video game mod of all time is for certain Max Payne: Kung Fu Edition, which, despite being by far the most accomplished Payne mod code-wise and having a ton of custom animations high quality enough to fit in with the rest of Max Payne, was developed by one guy, Kenneth Yeung. Here's some gameplay I recorded just now:
https://youtu.be/YGkFDvx6FQM
There were a lot of Max Payne mods, but this was the only one that really reached farther than custom weapons, levels and characters - not many had custom animations or code. This had far more developed melee combat, combos, a leveling-up system, wall jumps and wall runs, and different "shootdodge" moves when you used it standing vs running. Nobody else had figured out how to do any of that stuff! And besides being an incredible achievement, it also just plays really well, even today - it and Oni will be my main touchstones if I ever get to make the melee-heavy shooter of my dreams.
A while back I tweeted at Kenneth about a vague memory I had of something he'd done to get beyond the limits of Max Payne's modding tools:
me:
Hey man, I have a vague memory of a Kung Fu mod dev anecdote of yours where...something like, you weren't able to do a trace for your wall run in the MP1 SDK, so you used a bullet? Wondering if that rings any bells for ya
Kenneth Yeung:
Wow, you have an incredible memory of 20 years ago! And yes you're spot on, let me take a trip down Max Payne modding memory lane...
The game didn't have any sort of special event triggers for player collisions with arbitrary walls, but I noticed that bullets COULD differentiate between different collision types (ie they could trigger a different event when hitting a character vs hitting level geometry). So in the player's sideways jump animation, I shot tons of invisible and extremely short-lived bullets sideways out of your hip, which would spawn a special type of explosion only if they hit a wall.
That explosion only affected the player, and if the player ever took damage from that explosion type it would immediately kick you into a wall run animation. There were multiple versions of these special bullets+explosions, one for each wall run and wall jump direction.
A similar thing was used for the player's attack combo animations. There were invisible bullets flying out of your fists and feet, looking for collisions with enemy bodies in order to trigger the next animation in the attack combo.
I eventually ran out of new animations you were allowed to add to a character, which is when I stopped adding more moves. But that's probably a good thing, otherwise I might have been working on the kung fu mod for 10 more years.
Here's the trailer from back in the day, which had me hyped as hell - note the Jet Li and Jackie Chan models, which didn't end up coming with the mod (and you can barely tell who they are here, but the screenshots on the mod's website looked really good).
https://www.youtube.com/watch?v=CnegCg638N4
Also, Remedy added an homage to the mod as an easter egg in Max Payne 2, which is lovely: