I'm at work so I'm not going to try and do some experiments, but my guess at relative frequency of (under)promotion being the best move (Queen = 1) is something like;
Knight 1e-3
Rook 1e-5
Bishop 1e-7
A complicating factor in measuring this is that often a rook serves just as well as a queen, the opponent is going to capture immediately anyway, so players sometimes underpromote to a rook just for the fun factor. My number is more meant to represent cases where underpromotion really is the best move. Knight underpromotion is by far the most important, because a knight can do things a Queen can't, most typically give check to keep a combination going without giving the opponent an opportunity to play an active move. Rook and Bishop underpromotions can only be advantageous when they simultaneously avoid stalemate and keep a winning advantage. This is much more likely with a rook, for example in the famous Saavedra position, perhaps the most famous chess problem of all (it featured as decoration on our wedding cake :).
It is extremely rare. I saw an example involving Fabiano Caruana(current world #2)[1] and he was visibly excited in the post-match interview and said that's the first time in his whole career (which is thousands and possibly tens of thousands of games especially if you consider online etc) that he had done it.
Edit to add: Here's the game. It was Caruana vs Robson 2021 and occurred in the 7th round of the USCF championship. Fabi's final move was to promote to bishop at which point Ray Robson resigned https://lichess.org/study/embed/iLDop9iy/wCdsPJJI He did it for style points- a queen would have been just fine in that position.
