>CROSS APPLY solution looks very intuitive to me as well. That's at least my first thought without even trying.

In the past I went for the CTE or correlated subquery approach for this - it was actually some of your prior code posts that made me realize CROSS APPLY was also good for this kind of pattern, what I call a variation of a "perform varying pattern"