Soooo... what is it supposed to do? Looks like a million sub-selects and joins! Already have a headache just with looking at it. I'd probably right it all over

Soooo... what is it supposed to do? Looks like a million sub-selects and joins! Already have a headache just with looking at it. I'd probably right it all over again from scratch.