There's a single GIF I could make that explains exactly why I got into mapping. A part of it would be this:
All that destruction For me, it's fun to make, and fun to play. I just love explosions in maps. Boom boom. BOOM!
BOOOOOOM As for other reasons why I'm mapping and modding:
I like creating things. Having started working on a HL mod in 2015 made me start learning new skills: music production, modeling, coding and texturing.
Until then, I only knew some basic low-quality mapping and video editing. And this just opened up so many new opportunities. Now I'm planning to work on a game in Unreal Engine with a team, and I already am in 2 mod teams.
So TL;DR, it's fun, it's a nice brain exercise and it can even sometimes bring you some money.