How to get SQL output with more rows than original table? - Timeline of Events

Question

I have a table as follows:

event_num    occurs_at_time     length
1            0                  10
2            10                 3
3            20                 10
4            30                 5

Intended output:

start_time     length      event_type
0              10          Occurrence
10             3           Occurrence
13             7           Free Time
20             10          Occurrence
30             5           Occurrence

I'm having a hard time figuring out how to create a new row for Free Time in a SELECT statement. Free Time events occur whenever the difference between the next row occurs_at_time and previous row length + occurs_at_time is > 0.

For instance, between event_num 2 and event_num 3, 20 - 10 - 3 = 7 is the length and 10 + 3 = 13 will be the start_time.

I tried using LAG() and LEAD() window functions with CASE WHEN clauses to compare the next and previous rows, but I'm not sure how I can create a new row in the middle.

Answer 1

Use below

select * from (
  select occurs_at_time as start_time, 
    length, 
    'Occurrence' as event_type
  from your_table
  union all
  select occurs_at_time + length as start_time, 
    lead(occurs_at_time) over(order by occurs_at_time) - occurs_at_time - length as length, 
    'Free Time' event_type
  from your_table
)
where length > 0               

if applied to sample data in your question - output is