Calculate the time taken by a recursive function each time it is called… in SQL

I have the data like below

id | Function_name | event | time
1     function_a     start   12:01
2     function_a     start   12:02
3     function_a     start   12:03
4     function_a     end     12:04
5     function_a     start   12:05
6     function_a     end     12:06
7     function_a     end     12:07
8     function_a     end     12:08

In the above data, function_a is called 4 times recursively and the start and end of the function are logged. I need to capture the time taken for each function call. The output for the above table should look like below.

Function_call_id | time_taken_for_function_call(seconds)
1                   7
2                   5
3                   1
4                   1

Answer

You can enumerate the calls and then aggregate. This enumerates the starts and stops

select t.*,
       (next_time - time) as diff
from (select t.*, lead(time) over (partition by grp order by id) as next_time
      from (select t.*,
                   (sum(case when event = 'start' then 1 else -1 end) over (order by id) +
                    (case when event = 'end' then 1 else 0 end)
                   ) as grp
            from t
           ) t
     ) t
where event = 'start';