Previous quarter max month value in big Query

Pihu 0
Viewed 49

I want to see the previous quarter's max month(as a new column) value in the current quarter using a big query.

  • When in Q1 2022 it should display Q4 December 2021 as a new column
  • When in Q2 2022 it should display Q1 March 2022 (in this case 60000)
  • When in Q3 2022 it should display Q2 June 2022 (in this case 40000)

My data is like below

date        Sales
2022-09-01  10000
2022-08-02  20000
2022-07-01  30000
2022-06-01  40000
2022-05-01  30000
2022-04-01  50000
2022-03-01  60000
2022-02-01  10000
2022-01-01  89090

Output

enter image description here

date google-bigquery
1 Answers

your given result table fits not the task: previous quarter's max month.

Here several outputs. Do you want the maximum of the last months, or the values from three month ago? Both columns are included here.

The formula of the 1st month of quarter can be edit to the last month by changing the 1 to -1. As You want zero values for all other months, you need to multiply this with the other column.

Window function do the job. But for each month there must be one row. This is filled up with the all_months table.

with tbl as 
(
Select date("2022-09-01") as dates,  10000 money
union all select date("2022-08-02"),  20000
union all select date("2022-07-01"),  30000
union all select date("2022-06-01"),  40000
union all select date("2022-05-01"),  30000
union all select date("2022-04-01"),  50000
union all select date("2022-03-01"),  60000
union all select date("2022-02-01"),  10000
union all select date("2022-01-01"),  89090

),
all_months as 
(select dates,0 from (Select max(dates) A, min(dates) B from tbl), unnest(generate_date_array(A,B,interval 1 month)) dates)

select *,
if( date_trunc(dates,quarter)= date_trunc(date_sub(dates,interval 1 month),quarter),0,1) as first_month_of_quarter, 
lag(money_max_this_quarter) over (order by dates) as money_max_last_quarter,
lag(money,3) over (order by dates) as money_three_months_ago,
from
(
select * ,
max(money) over (partition by date_trunc(dates,quarter ) ) as money_max_this_quarter
from 
(
  Select dates,sum(money) as money from tbl group by 1
  union all select * from all_months
)
)
order by 1 desc
userG7m710
Related