I have a table sales with some columns and data like this:
SELECT order_date, sale FROM sales;
+------------+------+
| order_date | sale |
+------------+------+
| 2020-01-01 | 20 |
| 2020-01-02 | 25 |
| 2020-01-03 | 15 |
| 2020-01-04 | 30 |
| 2020-02-05 | 20 |
| 2020-02-10 | 20 |
| 2020-02-06 | 25 |
| 2020-03-07 | 15 |
| 2020-03-08 | 30 |
| 2020-03-09 | 20 |
| 2020-03-10 | 40 |
| 2020-04-01 | 20 |
| 2020-04-02 | 25 |
| 2020-04-03 | 10 |
+------------+------+
and I would like to calculate, for example, monthly growth rate.
From the previous data example the expected result would be like this:
month sale growth_rate
1 90 0
2 65 -27.78
3 105 61.54
4 55 -47.62
We have an old MySQL version, 5.x.
could anyone help or give me some clues to achieve this?
It is a bit complicate:
select
s.*
-- calculate rate
, ifnull(round((s.mnt_sale - n.mnt_sale)/n.mnt_sale * 10000)/100, 0) as growth_rate
from (
-- calculate monthly summary
select month(order_date) mnt, sum(sale) mnt_sale
from sales
group by mnt
) s
left join ( -- join next month summary
-- calculate monthly summary one more time
select month(order_date) mnt, sum(sale) mnt_sale
from sales
group by mnt) n on n.mnt = s.mnt - 1
;