Yes, I know how to do it conceptually in pure SQL. I also know how to do it reasonably quickly using Oracle's analytic extensions. I prefer the latter solution since it runs at an acceptable speed without having a DBA get involved in changing the schema.