It’s helpful to think of each
APPLYas a pipe operation, taking the values from the previous derived table and passing them into the next to be manipulated. Programming T-SQL in this manner (loosely) approximates modern functional programming techniques.
It keeps each step of the logic smaller, so that it’s easier to understand. And you can expose the intermediary columns to help with debugging.
This is one of my favorite uses of the APPLY operator, as it lets you think through a problem step-by-step while still allowing the optimizer to create a set-based solution for you.