I am too much of a wuss to mod different games but I love the results people posts of their GTA San Andreas, Skyrim and Breath of the Wild mods. I think they are pretty hilarious and creative. Also if I can be honest, I play mostly on my console.
As for me, one of the few games I've ever tried to mod is the sims 3. Then again talking with a friend he was pretty much saying downloading some mods to optimize the game in order to make it more stable, better organize resources and minimize the possibility of glitches (like resetting sims in case they have been stuck, turning off music from lots we're not visiting, etc) are a must if you have the game installed. That said, people have mods to add more actions to the sims, more flexibility creating them, etc. It is an interesting dive if you are interested in The Sims franchise.
The other one was Brawl, through homebrew. It was mostly to add different skins and scenarios for the game. It was pretty fun. Some remodelling was pretty impressive at the time. And having Mexican pop characters in the Smash games was pretty hilarious. Too bad I no longer have the instructions, files and assets to do replicate it.