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?
<>https://dbfiddle.uk?rdbms=postgres_9.6&fiddle=706ae237126b3f6badd955158be1811c
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:
<>https://dbfiddle.uk/?rdbms=postgres_9.6&fiddle=ec086d7078f3d3d9512dabbecce52355&hide=1
### *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.*
[1]: https://dbfiddle.uk/?rdbms=postgres_9.6&fiddle=706ae237126b3f6badd955158be1811c