Summing field in other rows conditionally

I have table in the form like below:

Pilot	Leg	Duration	Takeoff
John	1	60	9:00:00
John	2	60	9:00:00
John	3	30	9:00:00
Paul	1	60	12:00:00
Paul	2	30	12:00:00
Paul	3	30	12:00:00
Paul	4	60	12:00:00

And I am trying to figure out is a query to get the following:

Pilot	Leg	Duration	Takeoff	LegStart
John	1	60	9:00:00	9:00:00
John	2	60	9:00:00	10:00:00
John	3	30	9:00:00	10:30:00
Paul	1	60	12:00:00	12:00:00
Paul	2	30	12:00:00	13:00:00
Paul	3	30	12:00:00	13:30:00
Paul	4	60	12:00:00	14:00:00

So the ‘LegStart’ time is the ‘TakeOff’ time, plus the duration of prior legs for that pilot.

Now , to do this in SQL, I need to somehow add up the durations of prior legs for the same pilot. But for the life of me… I cannot figure out how you can do this because the pilots can have a variable number of legs, so joining doesn’t get you anywhere.

Answer

Try analytic SUM sum(duration) over (partition by pilot order by leg):

with mytable as (
  select 'John' as pilot, 1 as leg, 60 as duration, time '9:00:00' as takeoff union all
  select 'John', 2, 60, '9:00:00' union all
  select 'John', 3, 30, '9:00:00' union all
  select 'Paul', 1, 60, '12:00:00' union all
  select 'Paul', 2, 30, '12:00:00' union all
  select 'Paul', 3, 30, '12:00:00' union all
  select 'Paul', 4, 60, '12:00:00'
)
select
    *,
    time_add(takeoff, interval ifnull(sum(duration) over (partition by pilot order by leg ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0) minute) as legstart
from mytable