postgresql add tag
Anonymous 2139
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?


Formula for YoY:

June 2021 
((spend in 2021-06-30/ spend in 2020-06-30)-1)*100 = ((5680087/ 5344183)-1)*100=6.2

March 2021 
((spend in 2021-03-31/ spend in 2020-03-31)-1)*100=((5749467/ 5156327)-1)*100=11.5

Formula for QoQ:

June 2021 
((spend in 2021-06-30/ spend in 2021-03-31)-1)*100 = ((5680087/ 5344183)-1)*100=-1.2

March 2021 
((spend in 2021-03-31/ spend in 2020-12-31)-1)*100=((5749467/ 5156327)-1)*100=0.092


Top Answer
Andriy M
If the table is guaranteed to have exactly one row for every quarter, you could try using the `LAG` analytic function to fetch a previous `spend_dollars` value:

- for quarter over quarter
   LAG(spend_dollars, 1) OVER (ORDER BY report_date ASC)

- for year over year
   LAG(spend_dollars, 4) OVER (ORDER BY report_date ASC)

Here is a solution combining both results in one output:


### *Remarks*

1. *The [original fiddle][1] has what appears to be a typo for the `report_date` value in the 31-dec-19 row.  The value is specified to be 2019-09-30 whereas it should probably be 2019-12-31.  The latter is used by the fiddle linked in this answer.*

2. *Although the output examples in the question mention the report date of 2019-06-30, there is no such row in the original fiddle.  The solution fiddle linked above works with the same set of periods as the original.*


Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.