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.)