Search code examples
sqlgoogle-bigquerysql-insertwhere-clausewindow-functions

How would I conditionally insert a row based on the previous row?

I'm currently trying to figure out how I can conditionally insert a row based on a previous row. I'm fairly used to using window functions, and I figure I'll have to do so to make this work, but I don't know any other functions to make this work.

The dataset I'm working with would look like this

Original Table

And what I would want it to look like would be this:

After Modification

So, in effect, I'm looking to add the gap that exists between two row dates. If the end date of one row has a gap between it and the start date of the next row, I would want to be able to insert a row between them that has the same item and store with the in-between dates and a sold amount of 0.

I am trying to perform this in Google BigQuery console.

Solution

  • You can use union all and lead():

    select item, store, start, end, sold
from t
union all
select item, store, dateadd(end, interval 1 day), dateadd(next_start, interval -1 day)
from (select item, store, end, lead(start) over (partition item, store start) as next_start
      from t
     ) t
where next_start  dateadd(end, interval 1 day);