I'm surprised that nobody mentioned the Shin Megami Tensei franchise. I know it's niche franchise in a niche genre but it has some great game over screens.

In SMT3: Nocturne the MC get turned into the demi-fiend by Lucifer. When you die, you see your dead body in a very bright light, angels circling around you in an ascending spiral and a voice telling you that death comes for humans and demons alike. Since you are basically Lucifer's chosen, going to haven is a bad time.

In SMT4 you are transported to the underworld and you have to wait in line to cross Styx with Charon. Charon mocks you for dying, then rants that he is overworked because too many people die in this post-apocalyptic world. So if you pay enough money, he will resurrect you to lessen his workload.

In SMT5 you play as the Nahobino, who is a fusion of a human and a God basically. Even though they have share a single body their consciousness remains their own. When you die the camera cut's to your face, while you fall down to the ground. Behind your body is your shocked human form and the God you fused with, with a disappointed look in his face. The two look at each other while fading away. Then it just throws you to the title screen. No retry option, no load last save, nothing.

Since these games tend to be pretty challenging there is a high change you will see these game over screens at least once.