Ad

Subtract Dates From Array Bigquery

there is a table in BigQuery as shown below.

Table

I need to find how many days an element stayed in an specific status.

In this case for example
The Element1 stayed 13 days in Status1 and 33 days in Status2.
The Element2 stayed 32 days in Status1, 38 days in Status2 and 21 days in Status3.
The Element3 stayed 5 days in Status1.

I want to create a new column that returns the days. It would be like that.

Table2

The thing is, the number of status can go from 1 up to x (x = variable for total number of status), so for each element the number of status is unknown.

Ad

Answer

Consider below approach

select element, array(
  select as struct *, 
    date_diff(lead(date(entered_date)) over(order by entered_date), date(entered_date), day) days
  from t.statuses
) as statuses
from your_table t      

if applied to sample data in your question - output is

enter image description here

Ad
source: stackoverflow.com
Ad