SQL disaggregate monthly data into daily rows

Question

I'm using SQL in bigQuery

I need to disaggregate monthly data into daily entries and compute the total cost per day.

Input Data

StartDate    EndDate     IDCampaign  TotalCost
2024-01-01   2024-03-31  C1          50000
2024-02-01   2024-04-30  C2          12000

Output Data

StartDate    EndDate     IDCampaign  TotalCost
2024-01-01   2024-03-31  C1          555,55
2024-01-02   2024-03-31  C1          555,55
2024-01-03   2024-03-31  C1          555,55
2024-01-04   2024-03-31  C1          555,55
.
.
2024-03-31   2024-03-31  C1          555,55

I'm trying to do a CTE using GENERATE_DATE_ARRAY but I can not perform de calculation. Do you have any ideas? or maybe to improve this query:

WITH DateRange AS (
  SELECT 
       StartDate, 
       EndDate
   FROM tmptable
)
SELECT 
  Day
FROM DateRange,
UNNEST(GENERATE_DATE_ARRAY(StartDate, EndDate, INTERVAL 1 DAY)) AS Day;

Thanks in advance.

Answer 1

Consider below approach (BigQuery)

select day as StartDate, EndDate, IDCampaign, cost as TotalCost
from your_table,
unnest(generate_date_array(StartDate, EndDate)) day,
unnest([TotalCost / date_diff(EndDate, StartDate, day)]) cost  

if applied to sample data in your question - output is