I have a table like:
month | type | email
----- ------ ------
2015-08-01 | questionnaire | email1@gmail.com
----- ------ ------
2016-08-01 | creation | email1@gmail.com
----- ------ ------
2018-12-01 | buy | email1@gmail.com
----- ------ ------
2020-02-01 | questionnaire | email2@gmail.com
----- ------ ------
2021-08-01 | creation | email2@gmail.com
----- ------ ------
2021-11-01 | buy | email2@gmail.com
I want to generate a table that has, for all emails, every month since min(month) until current_date(). As for the type column, the value should be the "strongest" with questionnaire < creation < buy.
For example:
- if on 2018-08-01, I have a type buy, then all months until current_date() should have this type.
- if on 2017_12-01, I have a type qustionnaire, and then, on 2020-01-01 I have a creation type, then all month from 2017_12-01 until 2020-01-01 should be questionnaire type. Then, from 2020-01-01 until current_date, it should be creation type.
Any idea how to do it in bigquery?
One additional comment: It's very important to respect the priority questionnaire < creation < buy. If the first event type that happens is the strongest ("buy"), then all following months will necessary be a "buy" type. A type cannot go to a lower type later on.
CodePudding user response：
Try this one:
with mytable as (
select date '2021-05-01' as month, "questionnaire" as type, "[email protected]" as email union all
select date '2021-08-01' as month, "buy" as type, "[email protected]" as email union all
select date '2021-10-01' as month, "creation" as type, "[email protected]" union all
select date '2021-06-01' as month, "questionnaire" as type, "[email protected]" as email union all
select date '2021-08-01' as month, "creation" as type, "[email protected]" as email union all
select date '2021-10-01' as month, "buy" as type, "[email protected]"
)
select
monthly as month,
MIN(mytable.type) OVER (PARTITION BY emails.email ORDER BY monthly ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as computed_type,
emails.email
from (select distinct email from mytable) as emails
cross join unnest(GENERATE_DATE_ARRAY((select min(month) from mytable), current_date(), INTERVAL 1 MONTH)) as monthly
left join mytable on emails.email = mytable.email and monthly = mytable.month
where true
qualify computed_type is not null
order by 3, 1
CodePudding user response：
Consider below approach - much cheaper, less verbose, etc.
select month, email, type
from (
select *, ifnull(lead(month) over(partition by email order by month) - 1, current_date) next_month
from your_table
), unnest(generate_date_array(month, next_month, interval 1 month)) month
order by email, month
if applied to sample data in your question - output is
month email type
2015-08-01 email1@gmail.com questionnaire
2015-09-01 email1@gmail.com questionnaire
2015-10-01 email1@gmail.com questionnaire
2015-11-01 email1@gmail.com questionnaire
2015-12-01 email1@gmail.com questionnaire
2016-01-01 email1@gmail.com questionnaire
2016-02-01 email1@gmail.com questionnaire
2016-03-01 email1@gmail.com questionnaire
2016-04-01 email1@gmail.com questionnaire
2016-05-01 email1@gmail.com questionnaire
2016-06-01 email1@gmail.com questionnaire
2016-07-01 email1@gmail.com questionnaire
2016-08-01 email1@gmail.com creation
2016-09-01 email1@gmail.com creation
2016-10-01 email1@gmail.com creation
2016-11-01 email1@gmail.com creation
2016-12-01 email1@gmail.com creation
2017-01-01 email1@gmail.com creation
2017-02-01 email1@gmail.com creation
2017-03-01 email1@gmail.com creation
2017-04-01 email1@gmail.com creation
2017-05-01 email1@gmail.com creation
2017-06-01 email1@gmail.com creation
2017-07-01 email1@gmail.com creation
2017-08-01 email1@gmail.com creation
2017-09-01 email1@gmail.com creation
2017-10-01 email1@gmail.com creation
2017-11-01 email1@gmail.com creation
2017-12-01 email1@gmail.com creation
2018-01-01 email1@gmail.com creation
2018-02-01 email1@gmail.com creation
2018-03-01 email1@gmail.com creation
2018-04-01 email1@gmail.com creation
2018-05-01 email1@gmail.com creation
2018-06-01 email1@gmail.com creation
2018-07-01 email1@gmail.com creation
2018-08-01 email1@gmail.com creation
2018-09-01 email1@gmail.com creation
2018-10-01 email1@gmail.com creation
2018-11-01 email1@gmail.com creation
2018-12-01 email1@gmail.com buy
2019-01-01 email1@gmail.com buy
2019-02-01 email1@gmail.com buy
2019-03-01 email1@gmail.com buy
2019-04-01 email1@gmail.com buy
2019-05-01 email1@gmail.com buy
2019-06-01 email1@gmail.com buy
2019-07-01 email1@gmail.com buy
2019-08-01 email1@gmail.com buy
2019-09-01 email1@gmail.com buy
2019-10-01 email1@gmail.com buy
2019-11-01 email1@gmail.com buy
2019-12-01 email1@gmail.com buy
2020-01-01 email1@gmail.com buy
2020-02-01 email1@gmail.com buy
2020-03-01 email1@gmail.com buy
2020-04-01 email1@gmail.com buy
2020-05-01 email1@gmail.com buy
2020-06-01 email1@gmail.com buy
2020-07-01 email1@gmail.com buy
2020-08-01 email1@gmail.com buy
2020-09-01 email1@gmail.com buy
2020-10-01 email1@gmail.com buy
2020-11-01 email1@gmail.com buy
2020-12-01 email1@gmail.com buy
2021-01-01 email1@gmail.com buy
2021-02-01 email1@gmail.com buy
2021-03-01 email1@gmail.com buy
2021-04-01 email1@gmail.com buy
2021-05-01 email1@gmail.com buy
2021-06-01 email1@gmail.com buy
2021-07-01 email1@gmail.com buy
2021-08-01 email1@gmail.com buy
2021-09-01 email1@gmail.com buy
2021-10-01 email1@gmail.com buy
2021-11-01 email1@gmail.com buy
2020-02-01 email2@gmail.com questionnaire
2020-03-01 email2@gmail.com questionnaire
2020-04-01 email2@gmail.com questionnaire
2020-05-01 email2@gmail.com questionnaire
2020-06-01 email2@gmail.com questionnaire
2020-07-01 email2@gmail.com questionnaire
2020-08-01 email2@gmail.com questionnaire
2020-09-01 email2@gmail.com questionnaire
2020-10-01 email2@gmail.com questionnaire
2020-11-01 email2@gmail.com questionnaire
2020-12-01 email2@gmail.com questionnaire
2021-01-01 email2@gmail.com questionnaire
2021-02-01 email2@gmail.com questionnaire
2021-03-01 email2@gmail.com questionnaire
2021-04-01 email2@gmail.com questionnaire
2021-05-01 email2@gmail.com questionnaire
2021-06-01 email2@gmail.com questionnaire
2021-07-01 email2@gmail.com questionnaire
2021-08-01 email2@gmail.com creation
2021-09-01 email2@gmail.com creation
2021-10-01 email2@gmail.com creation
2021-11-01 email2@gmail.com buy