For “scare” here, I don’t mean I got suckered by a jump scare. I mean the scare stayed with me long after the movie ended - sometimes for months.
Two come to mind although I’m sure there’s more. One is Nightmare on Elm Street. I saw the first one as a kid when it came out, and I thought it was genuinely scary. I’ve been a horror fan since childhood but that was one of the first visual scare movies that actually scared me. I had watched the slasher films that became franchises, and Freddy went there in the very next movie, but that first one was solid (plus I was young and it was a creepy idea).
Since you also mention tension I’ll go in the other direction and say Blair Witch. I was completely freaked out when I walked out of the theater. I watched it cold - no prior knowledge. It was close to opening weekend and I liked the poster. I hadn’t heard any hype. That movie both established that style of filmmaking and has yet to be equaled in my opinion.
I probably watch more horror than any other genre, and I don’t do it to get scared. I enjoy the humor, I enjoy the stories, and I enjoy the effects. I will rarely get caught by jump scares (although I might if the person next to me yells), and I almost never get actually scared. I can acknowledge when a story is creepy-cool (like Babadook) or creep-because-the-metaphor-is-too-on-the-nose like Pontypool, but for the most part I just enjoy them.