Love me some lateral join / cross apply. Correlated subqueries and visibility in the where clause?! Whoo.