Generate values on Bigquery based on previous months

user_17394062 0
Viewed 72

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.

google-bigquery sql
2 Answers

Try this one:

with mytable as (
    select date '2021-05-01' as month, "questionnaire" as type, "email1@gmail.com" as email union all
    select date '2021-08-01' as month, "buy" as type, "email1@gmail.com" as email union all
    select date '2021-10-01' as month, "creation" as type, "email1@gmail.com" union all
    select date '2021-06-01' as month, "questionnaire" as type, "email2@gmail.com" as email union all
    select date '2021-08-01' as month, "creation" as type, "email2@gmail.com" as email union all
    select date '2021-10-01' as month, "buy" as type, "email2@gmail.com"
)
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

enter image description here

user_134735250

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
user_52219440
Related