Looking at that SQL reminds me how much better Oracle & MS SQL Server are at doing queries like that (thanks to analytic functions). You could get that table access down to 1 single index scan, instead of 4+.

MySQL and Postgres are fine at OLTP but when you get into analytics and reporting, they really show their lack of features.