Note that this question is touched upon here, but only for very large, expansive mods.

Also, I don't play a lot of games and have never installed a mod (and most mobile games don't allow it anyway and none on console) so this is mostly based on how much I like the concept of the mod and the footage I've seen of it, which is obviously to be taken with a grain of salt and is gonna be a lot different from a list from someone who actually plays the game with the mod. This list will also be biased towards well known mods and won't be expansive for the same reason.

KSP

Principia

Kerbal space program simplifies gravity by only doing calculations for one body generating gravity, meaning Lagrange points don't exist and orbits are generally simplified. This mod changes that, and not just for your ship, it does for the planets too.

RSS (Real Solar System) mod

Title. Basically it recreates the real solar system in Kerbal space program since KSP has it's own system with it's own planets. Usually followed by Realism Overhaul (a mod with includes real rockets because the KSP system is 10 times smaller for playability) and Realistic progression (a mod that makes all those rockets disponible in the game's tech tree and would probably be high on this list.)

Outer planets mod

Basically a mod that adds Saturn, Uranus and Neptune analogues to the game, and some of their associated satellites, while still being relatively original. (note that they do add a pluto analogue but the stock game already has a pluto analogue. It's turned into an Enceladus analogue around the Saturn analogue.)

Honorable mentions:

10x Kerbol system, because it scales up all the celestial bodies in the game to a realistic size without needing to change the rest of the game.

Tilt' em!, for adding axial tilt into the game, something that is moderately important to orbital mechanics (if your planet is inclined relative to the rest of the solar system, your orbit might need to align with the solar system instead of the planet to reduce inclination in interplanetary space.

I personally usually heavily mod my games, due to finding the process of modding itself fun, along with me preferring to spend a LONG time at one run through.

As an example, I'd give minecraft where I usually play so-called expert packs, which are basically taking a lot of mods and making them depend on each other for progression.

I grew up playing DOOM, but if I were to boot up the original game I would find it frustrating to play by modern standards (e.g. mouse movement?!). Thankfully, there are a ton of source ports that modernize the engine and make the game more accessible to current gaming sensibilities (e.g. WASD-movement). They're basically community remasters of the game.

It got me wondering about what other games have strongly benefited from tools that exist outside of the game itself.

Examples of what I mean:

1. Source ports (e.g. DOOM)
2. Community bugfixes (e.g. Vampire: The Masquerade - Bloodlines)
3. Restored/extended content (e.g. Star Wars: Knights of the Old Republic II)
4. Significant mods (e.g. Frostfall for Skyrim)
5. Rulesets/challenges (e.g. Nuzlocke Challenge for Pokémon)
6. Anything else that doesn't fit the above categories

I don't mean with remastered graphics. I just mean the original Doom games. The reason for this is to have a better interface, some good gameplay tweaks (ie. freelook) and a way better multiplayer framework. I know GZDoom does most of those things but it doesn't have a very good multiplayer framework (lots of desyncing) and it would have an even better interface. Another solution to this problem that's not running Doom 1 in Doom 3 if also very welcome. Thanks and sorry for the wall of text.