My table shows the total budget of marketing campaigns for a given period:

I would like to distribute the total budget per campaign linearly over the days.

Therefore, the result should look like this:

I've come across GENERATE_DATE_ARRAY. However, splitting the budget out to a single date per row has been the hard part.

SELECT period
FROM (SELECT MIN(CAST(StartDate AS DATE)) as min_date,
             MAX(CAST(EndDate)) as max_date
      FROM total_budget
     ) tb JOIN
     UNNEST(GENERATE_DATE_ARRAY(tb.min_date, tb.max_date)) period

Would be glad about help!

CodePudding user response：

You may consider below approach.

WITH sample_table AS (
  SELECT 'Campaign-1' campaign_name, DATE '2023-01-01' start_date, DATE'2023-01-04' end_date, 100 budget_total UNION ALL
  SELECT 'Campaign-2' campaign_name, '2023-01-15', '2023-01-21', 210
)
SELECT campaign_name, date, budget_total / (DATE_DIFF(end_date, start_date, DAY)   1) AS budget_daily
  FROM sample_table, UNNEST(GENERATE_DATE_ARRAY(start_date, end_date)) date;

Query results

CodePudding user response：

Consider also below approach

select campaign_name, date, 
  budget_total / count(*) over(partition by campaign_name) as budget_daily
from your_table, unnest(generate_date_array(start_date, end_date)) date        

if applied to sample data in your question - output is