I always thought this was a nice feature to have between arcades and a majority of console games when you’re not busy playing them. They seem to be going the way side (at home), but some still have it and work well as a nice showcase.
My favorites have always been Soul Blade (home version) and the Resident Evil series.
The first few that come to mind that I just love to leave running would be Super Metroid and Point Blank 1 and 2. Agree with @D.Lo on the CG but despite having some of that Point Blank is still cool.
Super Metroid is a brilliant one. Folds into the intro, it looks like just regular gameplay, but slips in secret moves like the shinespark and crystal flash.
Similarly, while contradicting what I said above because it’s CG, Silent Hill’s attract mode contains story that isn’t anywhere else in the game. And if you finish with the best ending it has a change, which is quite brilliant as it makes the story an endless circle (and makes all the sequels impossible/redundant, but anyway…)
SNK has phenomenal attract modes on the NEO GEO. Just too many to list honestly.
A specific favorite of mine is the one they have for Metal Slug X. It features a sequence of 2P action where both players really know how to cover for each other and are causing absolute mayhem on screen.
Another great one was SSB on N64. It has gameplay plus bios on all the characters.
Sonic The Hedgehog has a pretty cool one. If you press A B & C simultaneously, Sonic starts doing stupid actions, and of you let them go, he’ll try to do his usual routine, but hilariously messed up.
Metroid fusion also has the “tutorial” demo, in which it teaches you abilities and secret locations of you decide to watch it. Super Pitfall has the infamous clear game demo “glitch” and a couple of NES games have the “this game is so hard, even the demo player can’t survive for very long” demos.
My favorite and more nostalgic one has to be Super Mario World. Not only it shows a super secret stage, it also serves as the main menu (and the music really catches what that game is all about).
Ninja Gaiden NES (all 3) had those amazing attract modes with cutscenes, and thus beginning the cinemátic experience in games.
Wild Arms had one too, and it takes quite a while to start, but the story sequence explains a lot of things that’s aren’t brought up in the main game, and I only realized that it existed several years later on my second play through.
Glad to see R&D1’s Metroid games mentioned as good attract modes to show the player advanced skills they might not know. Super Metroid, Zero Mission and Fusion all have great ones.
On that subject: Ninja Cop’s is great! I had no idea Ninpo was in the game - or that you could attack while swinging - until booting up the game one day and seeing it there!
While we’re at it, Fire Emblem has made a big deal about showing off character classes in its attract modes. Fire Emblem 7 on GBA’s starts playing seconds after the intro finishes. Good timing:
Used to love watching Sega Rally in the window of Dixon’s. So much so that I chose to buy a Saturn over a PlayStation for that one game. So big and bold and colourful.