0

I have table in BigQuery which keeps track of spend amount for every quarter starting June 2019. I need to calculate Year over year and Month over Month percent change. I've mentioned the appropriate formula for both of them. Performing the calculation across rows for YoY and MoM is getting a bit tricky for me. Can someone help?

Table:

period      report_date  spend_dollar
30-jun-19   2019-06-30    5022087
30-sept-19  2019-09-30    4958617
31-dec-19   2019-09-30    5038630
31-mar-20   2020-03-31    5156327
30-jun-20   2020-06-30    5344183
30-sept-20  2020-09-30    5562796
31-dec-20   2020-12-31    5696796
31-mar-21.  2021-03-31    5749467
30-jun-20.  2021-06-30    5680087

Expected output for YoY:

period      report_date  spend_dollar.   year_over_year
30-jun-19   2019-06-30    5022087        -
30-sept-19  2019-09-30    4958617      -
31-dec-19   2019-09-30    5038630        -
31-mar-20   2020-03-31    5156327        -
30-jun-20   2020-06-30    5344183        6.4
30-sept-20  2020-09-30    5562796        12.18
31-dec-20   2020-12-31    5696796        13.1
31-mar-21.  2021-03-31    5749467        11.5
30-jun-20.  2021-06-30    5680087        6.3
YoY formula example formula: 
((spend_dollar value for date 2021-06-30/spend_dollar for date 2020--06-30)-1)*100, 
((spend_dollar value for date 2020-03-30/spend_dollar for date 2020--03-30)-1)*100 
..

Expected output for month over month:

period      report_date  spend_dollar.   month_over_month
30-jun-19   2019-06-30    5022087        -
30-sept-19  2019-09-30    4958617.       -1.2
31-dec-19   2019-09-30    5038630.       1.6
31-mar-20   2020-03-31    5156327        2.3
30-jun-20   2020-06-30    5344183.       6.4
30-sept-20  2020-09-30    5562796.       4.1
31-dec-20   2020-12-31    5696796        2.4
31-mar-21.  2021-03-31    5749467        0.092
30-jun-20.  2021-06-30    5680087        -1.2
Month-over-Month example formula: 
((spend_dollar value for date 2021-06-30/spend_dollar for date 2021--03-30)-1)*100 ,
((spend_dollar value for date 2021-03-30/spend_dollar for date 2020--12-31)-1)*100
..


2
0

What you need is what are normally called "window functions" in SQL but BigQuery appears to have different names - it calls some of them "navigation functions" for example.

I'm not a BigQuery expert, but if I were you, what I would do first is to look at the BigQuery documentation:

Window functions are very powerful (brief PostgreSQL tutorial here - a good series of tutorials here) and will repay any effort spent learning them many times over.

The functionality all appears to be compatible with PostgreSQL, so the example below should work well for you. All of the code below is available on the PostgreSQL fiddle here.

You require something like this:

SELECT 
  period, spend_dollars, 

  ROUND((spend_dollars / LAG(spend_dollars, 1)      ---  <<=== note offset 1!
    OVER (ORDER BY period))::NUMERIC * 100, 2) AS quarter,  

  ROUND((spend_dollars / LAG(spend_dollars, 4)      ---  <<=== note offset 4!
    OVER (ORDER BY period))::NUMERIC * 100, 2) AS annual
    
FROM product_spend
ORDER BY period;

Result:

period     spend_dollars  quarter    annual
30-jun-20   5344183         
30-jun-21   5680087        106.29   
30-sept-19  4958617         87.30   
30-sept-20  5562796        112.18   
31-dec-19   5038630         90.58    94.28
31-dec-20   5696796        113.06   100.29
31-mar-20   5156327         90.51   103.99
31-mar-21   5749467        111.50   103.36

Note that with with LAG() (and LEAD()) functions, there is an offset

(LAG (value_expression[, offset [, default_expression]])) 

which you can use to determine the gap between the current record and the value obtained from the function.

This means that you can obtain analyses over an annual period (4 period offset) or just a quarter (offset 1) (or monthly, weekly... whatever your data allows), even within the same query!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.