Query to calculate average time between successive events

Question

My question is about how to write an SQL query to calculate the average time between successive events.

I have a small table:

event Name    |    Time

stage 1       |    10:01
stage 2       |    10:03
stage 3       |    10:06
stage 1       |    10:10
stage 2       |    10:15
stage 3       |    10:21
stage 1       |    10:22
stage 2       |    10:23
stage 3       |    10:29

I want to build a query that get as an answer the average of the times between stage(i) and stage(i+1).

For example, the average time between stage 2 and stage 3 is 5:

(3+6+6)/3 =  5

Answer 1

Aaaaand with a sprinkle of black magic:

select a.eventName, b.eventName, AVG(DATEDIFF(MINUTE, a.[Time], b.[Time])) as Average from
     (select *, row_number() over (order by [time]) rn from events) a
join (select *, row_number() over (order by [time]) rn from events) b on (a.rn=b.rn-1)
group by
a.eventName, b.eventName

This will give you rows like:

stage3  stage1  2
stage1  stage2  2
stage2  stage3  5

The first column is the starting event, the second column is the ending event. If there is Event 3 right after Event 1, that will be listed as well. Otherwise you should provide some criteria as to which stage follows which stage, so the times are calculated only between those.

Added: This should work OK on both Transact-SQL (MSSQL, Sybase) and PL/SQL (Oracle, PostgreSQL). However I haven't tested it and there could still be syntax errors. This will NOT work on any edition of MySQL.