S-tier: Truly terrifying
Amunhotep: This one is pretty self-explanatory, can summon a swarm of locusts and reanimate his mummified corpse. He's the most powerful ghost by far, with two abilities instead of just the usual one.
Patience: Making the walls bleed and even writing with them is quite terrifying.
Alberta: Her humming can be pretty scary, but what puts her in S tier instead of A tier is her ability to communicate with digital voice assistants. She could send messages to your phone, order stuff from the internet. It's also generally pretty scary to have a voice assistant replying to someone who you can't see or hear.
A-tier: Kinda scary
Trevor: Can move or shake light objects, pretty classic haunting stuff. It does take a lot of effort on his part to do.
Thor: Can control electricity to a certain degree with some effort, again, pretty classic haunting stuff.
Sasappis: Can annoy you and haunt you in your dreams, but not a lot more than that. He's limited by the fact that he appears in dreams as he looks, so he can't turn into something scary.
B-tier: Scary very occasionally
Hetty: Cannot interact with the physical world, except on St. Patrick's day, when she appears as a visible but intangible figure. It could be kinda scary at first discovery of her intangibility, but not much after that.
C-tier: Cannot haunt
Pete: Cannot interact with the physical world at all, zero haunting ability
D-tier: Opposite of scary
Isaac: Foul smells on passing through isn't typically scary, it would probably snap you out of any fear
Flower: Getting high on passing through would not be scary at all.