HOW TO GET SEQUENTIAL DATE RANGE IN BIQUERY-CodePudding

I have data like the following enter image description here

I want to get a table like this. enter image description here

how can i get the active date as drawn...? Please help

CodePudding user response：

You can achieve what you want by using the GENERATE_DATE_ARRAY function in BigQuery. More specifically:

WITH customers AS (
SELECT 123456 as customer_id,
DATE("2022-01-01") as start_date,
DATE("2022-01-03") as last_active
)

SELECT customer_id, start_date, last_active, active_ 
FROM customers, UNNEST(GENERATE_DATE_ARRAY(start_date, last_active, INTERVAL 1 DAY)) as active_