I am doing expense analysis with Bigquery. I have a table with start_date, end_date, expense and I have to bring the expense to all days in the range between start_date and end_date. My table looks like:

My expectation should be:

CodePudding user response：

Consider below approach

select *, 
  round(Amount / (1   date_diff(End_Date,Start_Date, day)), 2) as Daily_expense
from data, 
unnest(generate_date_array(Start_Date, End_Date)) Daily_date           

if applied to sample data in your question - output is