This question was asked on dba.se

My accepted answer uses two levels of analytic (windowing) functions to get the desired result in a single SQL statement. Not all RDBMSs provide analytic functions but the question was specifically for Oracle which is one of those that do.

If it had instead been asked on SF, it should have been migrated to dba.se (rather than SO) because it is a specialized SQL question more suited to that site.

please note I'm not necessarily saying I really think questions like this should be migrated to dba.se rather than SO, rather I'm posting an answer to get feedback on what the SF community think is right. Please upvote/downvote this answer according to your preference

This question was asked on dba.se

My accepted answer uses two levels of analytic (windowing) functions to get the desired result in a single SQL statement. Not all RDBMSs provide analytic functions but the question was specifically for Oracle which is one of those that do.

If it had instead been asked on SF, it should have been migrated to dba.se (rather than SO) because it is a specialized SQL question more suited to that site.

please note I'm not necessarily saying I really think questions like this should be migrated to dba.se rather than SO, rather I'm posting an answer to get feedback on what the SF community think is right. Please upvote/downvote this answer according to your preference