Hmmm ... The grammar/authenticity etc is fine... The problem is what other people will classify as "the top". I imagine that many people will consider the blue cup to be the top, and therefore give you the yellow cup. I think that there will be always be potential confusion in this example, and the only way to avoid this is to clarify that you mean the red one. If you think about a ladder, the top is the highest point, but we can talk about the rungs (steps). In a three rung ladder, the second from the top is the closest to the floor. You can use the verb 'to stack' - The cups are stacked upside down.

I think ‘second from the top’ is a natural description of the position. However, the statement as a whole sounds unnatural because 1. They aren’t ‘cups’ , especially in a coffee shop. ‘Cups’ there are rounder and typically have handles. There are paper and plastic cups which look a little like the picture and are usually stacked upside down, but they stack much more tightly. I’m not sure I’ve seen anything like your picture, but I’d probably call them ‘glasses’ or ‘colored glasses’. 2. It’s unnatural to specify the specific container you want to be served in. If there are different types you can specify the type but ordinarily it’s the business that makes the specific choice of container. You’d need to explain yourself. So: Can you give me the red glass please? It’s my favorite color!

just say "I want the red one" -- why make a situation more complicated than it it?