MySQL query optimization
It may be miserably slow (until 5.6.7 or MariaDB 5.5) because of the following.  Let me give a simplified version:

SELECT ... FROM ( SELECT ... ) a
JOIN ( SELECT ... ) b ON a.x = b.x

There are (and cannot be) indexes on either of the temp tables (a, b).  If there are, say, 10K rows in each of a and b, then the JOIN will have to do 100M compares (10K*10K).  Slow.

If one of the subqueries boils down (via GROUP BY, LIMIT, or...) to only a few rows, then performance won't be too bad.

As a general rule, avoid subqueries.  Some subqueries are ok -- namely those that shrink the number of rows (GROUP BY, etc).  But the JOIN problem above still stands.

The general 'fix' for a subquery is to turn it into a JOIN.  But that does not apply here because most have GROUP BY.  So... Consider CREATEing TEMPORARY TABLEs with indexes for some or all of the subqueries.

Another performance killer is OR.  Generally it makes it impossible to use an index on the fields in the OR.  Sometimes the fix is to do two SELECTs and UNION ALL/DISTINCT them.

Wade through some of those suggestions, then come back for more advice.  Bring SHOW CREATE TABLE next time.