It's interesting that all these positions are called "common", but the actual board position might happen zero to one times in a lifetime, and I suspect it's usually zero times.
I noticed something similar when I played contract bridge at a competitive level. A top bridge player might play very roughly on the order of 10,000 hands a year, and vividly recall something that happens on the order of once a year as "oh yeah that's common". Of course I wasn't remotely close to them. But there is something about competitive games that seem to amplify the memory for certain kinds of unusual situations.
(Some people are commenting about under promoting to avoid stalemate traps down the line. I've always been a weak chess player, but... trying to set a stalemate trap after being down a queen, in a non-contrived position, is, like, adult chess players shouldn't do that. In my limited experience.)
Nowadays there are tournament matches with no resignations allowed, so setting stalemate traps may be more common from now on.
> vividly recall something that happens on the order of once a year as "oh yeah that's common".
I mean, think of how many times a typical person has sex in their life. Hopefully they and their partner aren't getting pregnant more than roughly once per year. But somebody getting pregnant after having sex is reasonably defined as common. Certainly common enough that it's something you would consider and take precautions to prevent if you didn't want it to happen.
In ranked chess games, underpromotion happens about 1 in 1000ish games. I imagine it would be more common in high level unranked play. If you play one chess game per day, that's once every 3 years on average. It's not frequent, but I'd describe that as common.