The one game that never fails to hook me in that way is Quake 3. Every mistake feels like your own, but with a very clear and obvious path for improvement. Every improvement step feels incremental and doable, none of that “noscope 180 from the bushes where i had zero chance to spot the enemy” situations.
Even on kills where it initially felt like some annoying cheap trick, i watch the killcam, and walk away very impressed and eager to try out their approach myself. And it always makes perfect sense. As opposed to a lot of more modern games, where killcam only frustrates me more.
Imo this is the same thing that made the original DOOM a massive sensation, but I feel like Quake 3 would be easier to relate to for a lot of people (on HN or elsewhere). At the time, I was too young and growing up in a wrong part of the world to catch that original DOOM hype. But with Quake 3 (and imo even quake 1 and 2), that phenomenon truly transcended countries and cultures.