So currently im currently doing a project on BQ to obtain monthly level data using the codes below.
WITH agg as ( SELECT DATE_TRUNC(CAST(date as DATE), MONTH) AS year_month, market, SUM(Rev) as Rev, FROM `data_table` group by 1,2)
However now i am required to get it at a 2 month level. Expected results would be like this.
|Jan 2021||JP||(sum of Jan & Feb Rev)|
|Mar 2021||JP||(sum of Mar & Apr Rev)|
|May 2021||JP||(sum of May & Jun Rev)|
|Jan 2021||MY||(sum of Jan & Feb Rev)|
|Mar 2021||MY||(sum of Mar & Apr Rev)|
|May 2021||MY||(sum of May & Jun Rev)|
Anyone got any ideas?