Bastion by Supergiant Games.
I'm actually not sure how precisely it happened, but a good decade ago as a teenager I somehow stumbled upon a torrent of the game. I didn't really know anything about the game, but I distinctly remember reading the description and looking at the art and being like "this is so cool", and then being like "this is even cooler" again when it turned out Rucks basically narrates the entire game with that deep hopeful voice.
And that was that; my early gaming continued with things like Hawken, Detective Grimoire and Machinarium, but Bastion definitely holds a special place in my heart.
More than a decade later, I've played (bought) and replayed basically all of Supergiant's games several times. Such a wonderful studio. Darren Korb is a fantastic composer as well, just doesn't miss a single vibe :)
I can relate to this very much. I love Team Fortress 2 - it has just enough of that random hilarious stuff in almost every match that makes you laugh. I think it's a huge part of why the game is still alive and broke its player record recently.
Too real (talking mostly about CS:GO as I that's the one I have most experience with on your list). It's... occasionally fun, especially if your team gets into a slighly less casual mindset and plays it a bit more tactically.
But it often ranges down to the collective team just getting mad all the time and throwing various accusations around for seemingly the fun(?) of it. Fun match? Maybe, sometimes. All the time? Absolutely not, thank you.
Over the years I've started reaching more and more for co-op instead (Deep Rock Galactic, PlateUp!, Alien Swarm, Minecraft, Unrailed, ...) and it has been a lot of fun, both solo and with friends.