To my knowledge, it varies by hotel, hotel management team to be more specific. I asked a few people who work at or used to work at various hotels from different chains, and I got different answers.

One answer I got, was upgrade was based on the availability of the room category you booked, not on the category above. If a room category is oversold, elite members will be upgraded to the category above. I don’t know how true this is or how many hotels/chains operate like this, but it does explain why I receive no upgrade when higher category rooms are indeed available.

I remember seeing a thread on Reddit once where some hotel employees explained how upgrades were done at their hotels.