By far the second most common piece to promote to is a knight, simply because a knight moves in a way a queen cannot. In my 10 years of playing chess, I have probably seen about 5-10 tournament games where promoting to a knight is the correct choice.
Here is a FEN of an example where the only saving move for Black is to play 1...e1=N+: "8/8/8/8/8/3K4/R3p3/3k4 b - - 0 1"
You are right in that the only reason to ever promote to a bishop or rook is to avoid stalemate, yet there is only one practical example of such, here: "8/2P5/8/8/3r4/8/2K5/k7 w - - 0 1".
White must promote to a rook, since if they promote to a queen Black will play 1...Rc4+, forcing 2...Qxc4, which is stalemate. I cannot even find a practical example of when promoting to a bishop would the best choice.