Instead of selecting directly from y, create an inline view that selects order_id and max of status date where status = booking and join onto that. You will still need to use group by, but only in the inline view.

Think about what you're asking - at some point in the processing, you need to look at all the rows in y for a specific order and get the one with the most recent date.

Now, depending on what percentage of the table Y you're going to be looking at, there are different approaches to this, but every single one of them will involve looking at more data and doing more work than your query does at the moment, but details like that are among the many many things that we need to know to give a proper answer, and that you don't tell us.