I think my most intense one was probably from the very first campaign I ever played (after a couple of sessions experience in a kinda ad-hoc fashion). D&D 4th edition.

We were a rather big group of (I think) 6 players plus DM. The main campaign was fairly standard. DM set us up with some prophecy stuff and we set about fulfilling it.

Everything was going pretty normally, until I got the weird sense that there was something else going on. Outside of session I spoke to the DM to have my character try and investigate. It turned out that weird sense was correct. Another player was setting up their own schemes between sessions. So I started setting up my own schemes, and roping other players into them.

It's the kind of thing that looking back on it now, most would probably consider a major red flag. There was no Session 0 or any out-of-character discussion about doing these kinds of things. It could have been incredibly toxic. But we loved it. It added a whole extra layer to everything we were doing during the sessions, as well as giving us the ability to converse with the DM and each other to play more of the game asynchronously between sessions.

In the penultimate session, we defeat the BBEG on another plane, and then arrive back in our world. Another week of last-minute scheming, before the actual final session was a massive PvP battle where the player openly turned against the rest of the party. One of the other players unexpectedly (to me at least) turned and joined them, as well as some NPCs on both sides. "Intense" is the perfect word to describe that session and the build up to it. That physical, heart-pumping feeling the same as when you're hyping yourself up for your first time trying a dangerous sport or something similar.

We ended up pushing back and winning the encounter for the good guys, but didn't actually kill or capture the betraying PC. They fled, and their player took over as DM for our next campaign.