Operation Bodycount recently came to GOG. Do NOT recommend, but it has a memorable game over screen.

https://www.youtube.com/watch?v=0uuE9xL95pU

We Love Katamari
The laser-dodging mini-game is a nice creative touch. Unfortunately, it is also a time-consuming to go through it in full, and I usually skip it after a while.

Final Fight series

Firestorm: Thunderhawk 2 aka Thunderstrike 2
It's mostly because of the music than anything else, it occassionally made me stay on the "game over" longer than I otherwise would.

Earthworm Jim definitely
https://www.youtube.com/watch?v=riZxpttwCeo

Favorite death scenes and bad endings :

   ▪  Styx: Shards of Darkness
   ▪  Batman Arkham series
   ▪  Myst

One of the worst game overs on the C64 :

   ▪  Chimera   ( headphones warning )

Just remembered an awful Game Over screen. Specifically, the one in I Wanna Be The Guy has some obnoxious music.

(It's one of the reasons I haven't tried that game myself, instead preferring Syobon Action when it comes to troll platformers.)

Elvira - Mistress of the Dark

https://www.youtube.com/watch?v=o-uJqHR5tt0

<3<3<3

The one that I relished the most is probably the crowning scene in "Kaiser".

I had managed to play through the game on my (well, our) C-64 while my brother was on a birthday party.
And to rub it under his nose, that I won the game before him, I let the C-64 run until he came home again - he was not amused. ;)

And he was even less amused, when I did the same after I became Bürgermeister in "Hanse". :D

For the worst "Game Over" screen...I guess I would have to name the game which simply ended with a message like "Congratulations! You made it. You defeated [whatever]."...and that was it.

But for my life's sake, I can't remember that game's name. I wonder why...

Why is this game not available here? It looks A-MA-ZING. Screw Skyrim, this should be GOG's priority!

What makes me like or hate a death screen is how fast it lets me open the load game menu. ;)

My favorite:

https://www.mobygames.com/images/shots/l/819956-the-secret-of-monkey-island-fm-towns-screenshot-jab-at-sierra.png

(no comment / spoiler)

The classic Sonic games' "game over" screens got very grating after failing a level so many times lol

this is the ultimate game over, nothing can ever beat it

https://www.youtube.com/watch?v=IsS_VMzY10I

I love the game over screens from the Final Fight games, where the protagonist is always about to die in some gruesome predicament. It reminds me of the cliffhangers from the 1960's Batman show.

Worst: None. It just kicks you out to the title screen/desktop or soft-locks the game. (Largely historical, though many A2600 titles would do this.)

Best (to mind): Earthbound. Rather than the bleaker side of things, it encourages you to get up to try again, or to take a break. It's also got this "Don't Give Up" vibe to it.

Classical Example: Exile/Avernum original trilogy. Give you can make some hilariously stupid choices (falling for obvious traps falling to your death, pushing the big red button, or drinking the acid), the sudden stock scream followed by a simple, "Well, there goes that effort. Try again?" sort of just sticks with you.