Translate date ranges to date sequences in BigQuery

I have table 1 that looks like the following:

+-----------+------------+------------+
| campaign  | start_date |  end_date  |
+-----------+------------+------------+
| campaign1 | 2020-01-01 | 2020-01-03 |
| campaign2 | 2020-01-04 | 2020-01-06 |
| ...       | ...        | ...        |
+-----------+------------+------------+

I’d like to create table 2 that looks like this:

+-----------+------------+
| campaign  |    date    |
+-----------+------------+
| campaign1 | 2020-01-01 |
| campaign1 | 2020-01-02 |
| campaign1 | 2020-01-03 |
| campaign2 | 2020-01-04 |
| campaign2 | 2020-01-05 |
| campaign2 | 2020-01-06 |
| ...       | ...        |
+-----------+------------+

Keep in mind that table 1 is going to have n number of rows and will be added to on a regular basis. I’d like to schedule the creation of table 2 using a scheduled query.

I’ve played around with GENERATE_DATE_ARRAY() in conjunction with CROSS JOIN UNNEST. I haven’t been able to find a way to do this elegantly. Any suggestions?

Answer

[How to] Translate date ranges to date sequences …

Below is for BigQuery Standard SQL

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 'campaign1' campaign, DATE '2020-01-01' start_date, DATE '2020-01-03' end_date UNION ALL
  SELECT 'campaign2', '2020-01-04', '2020-01-06' 
)
SELECT campaign, day
FROM `project.dataset.table`,
UNNEST(GENERATE_DATE_ARRAY(start_date, end_date)) day
-- ORDER BY campaign, day

with result

Row campaign    day  
1   campaign1   2020-01-01   
2   campaign1   2020-01-02   
3   campaign1   2020-01-03   
4   campaign2   2020-01-04   
5   campaign2   2020-01-05   
6   campaign2   2020-01-06   

Update – use below in your real use case (above was just example with dummy data from your question for you to test)

#standardSQL
SELECT campaign, day
FROM `project.dataset.table`,
UNNEST(GENERATE_DATE_ARRAY(start_date, end_date)) day