A queen is the best piece in almost every situation, so promoting to anything else needs a concrete reason. The most common is a knight promotion that gives check or forks the enemy king and queen, exploiting the one movement a queen lacks. Rook and bishop underpromotions appear mainly to avoid stalemate — taking a queen would leave the opponent with no legal move and only a draw.
Underpromotion is a favorite theme of composed studies and a satisfying resource to spot in practical play. Whenever a straightforward queening leads only to stalemate or fails to a defense, ask whether a minor promotion changes the verdict.