How to duplicate rows generating dates between Start Date and End Date in BigQuery?

Question

I'm having a question on how to duplicate rows in a table that generates dates between StartDate and EndDate. Actually, I have a same question as this Duplicating records to fill gap between dates in Google BigQuery but with a different type of table.

So, my table looks like this:

╔═══════════╦══════════════╦══════════════╗
║ user_name ║  start_date  ║   end_date   ║
╠═══════════╬══════════════╬══════════════╣
║     A     ║ '2019-07-01' ║ '2019-07-31' ║
╚═══════════╩══════════════╩══════════════╝

and I would like the output to be like this:

╔═══════════╦══════════════╦══════════════╦══════════════╗
║ user_name ║  start_date  ║   end_date   ║     date     ║
╠═══════════╬══════════════╬══════════════╬══════════════╣
║     A     ║ '2019-07-01' ║ '2019-07-31' ║ '2019-07-01' ║
╠═══════════╬══════════════╬══════════════╬══════════════╣
║     A     ║ '2019-07-01' ║ '2019-07-31' ║ '2019-07-02' ║
╠═══════════╬══════════════╬══════════════╬══════════════╣
║     A     ║ '2019-07-01' ║ '2019-07-31' ║ '2019-07-03' ║
╠═══════════╬══════════════╬══════════════╬══════════════╣
║     A     ║ '2019-07-01' ║ '2019-07-31' ║ '2019-07-04' ║
╠═══════════╬══════════════╬══════════════╬══════════════╣
║    ...    ║      ...     ║      ...     ║      ...     ║
╠═══════════╬══════════════╬══════════════╬══════════════╣
║     A     ║ '2019-07-01' ║ '2019-07-31' ║ '2019-07-31' ║
╚═══════════╩══════════════╩══════════════╩══════════════╝

How should I put it in a query? Thanks in advance.

See [How to generate date series to occupy absent dates in google BiqQuery?](https://stackoverflow.com/questions/38694040/how-to-generate-date-series-to-occupy-absent-dates-in-google-biqquery). — Tim Biegeleisen, Oct 16 '19 at 04:21
SQL is not very good at generating data, and what you are looking for is a calendar table. Then, just left join that calendar table to your table. — Tim Biegeleisen, Oct 16 '19 at 04:21

score 2 · Accepted Answer · answered Oct 16 '19 at 04:25

Below is for BigQuery Standard SQL

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 'A' user_name, DATE '2019-07-01' start_date, DATE '2019-07-31' end_date
)
SELECT user_name, start_date, end_date, day
FROM `project.dataset.table`, 
UNNEST(GENERATE_DATE_ARRAY(start_date, end_date)) day
ORDER BY user_name, day

with result

Row user_name   start_date  end_date    day  
1   A           2019-07-01  2019-07-31  2019-07-01   
2   A           2019-07-01  2019-07-31  2019-07-02   
3   A           2019-07-01  2019-07-31  2019-07-03   
. . . 
29  A           2019-07-01  2019-07-31  2019-07-29   
30  A           2019-07-01  2019-07-31  2019-07-30   
31  A           2019-07-01  2019-07-31  2019-07-31

How to duplicate rows generating dates between Start Date and End Date in BigQuery?

1 Answers1