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

![image.png](/image?hash=65f9a8f043c96d8970cb06be44875dc344832a65c0c4b4ce80964b9f5ea334ea)

![image.png](/image?hash=47af8e0aafbf3d7a505f45764880eff027936e65968742af01c6364cbedd2a5d)
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

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.