There are many (thousands of) endgame studies with great underpromotions like the Saavedra endgame (third diagram from the top). For instance, I completed one myself a few weeks ago - which obviously ranks among the greatest. A famous one you may have seen is a stalemate by 3 knights after a knight promotion. The fun is just as much in the (post-)promotion play as in the play leading op to it. Which is lacking in these examples.
A few years ago someone mined the endgame tablebase for all successful underpromotions in positions with at most 5 units (if my memory does not fail me). He found not hundreds but still quite a lot for such thin positions. He posted them on chess.com but I don't know how to find them!
Some of these positions have probably already been posted, but I figured we should have 1 thread with the most interesting underpromotion problems. Here are my favorites that I've seen: (Will continuously update the OP with the best examples in this thread). I didnt create any of these. They are from YouTube, this site, and from my chess books.
If white promotes to either a queen or rook, it's instant stalemate. If white promotes to a knight, the bishop just moves to g5 and the next move the white knight get captures and its white who's stalemate! This is one of the few positions where promoting to a bishop is the only way to win, not just a faster way to win. (Got this one from an underpromotion to bishop thread).
Simple underpromotion to rook endgame.
An even better bishop example.
One of the nicest rook underpromotion endgames, so simple yet the rook is so necessary (even a bishop stalemates).
White can promote both pawns to a queen, but either results in instant mate in 1 by the rook! Kf8 leads to the same mate, and kd8 allows the black king to win both pawns! F8 = N+ is the only winning move to deflect the black king from the mating net and then promote the g pawn to a queen winning! Very realistic endgame! It's also important to point out that unlike most underpromotion studies, where the underpromotion turns a loss into a draw, or an otherwise drawn game into a win, this is the only one I know of where the Underpromotion turns a Loss into a win. Any other piece promotion loses, and the only promotion that doesn't lose, wins.
Another simple underpromotion to bishop endgame where promotion to any other piece results in a draw.
Here's a couple positions where multiple underpromotions are necessary: