Difficulty in using LAG to catch the percentage of monthly evolution

Question

I have the following table below and would like to catch the monthly evolution (%) of the total transactions per month. I researched the LAG function but could not understand very well.

I need the feedback of this consultation to be like this:

MÊS | TOTAL TRANSACOES | % EVOLUCAO
-----------------------------------
09  | 45.561 | 0%

10  | 48.598 | 6.66%

SQL:

CREATE TABLE IF NOT EXISTS `campanha` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ano_mes` date DEFAULT NULL,
  `nome` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
  `cpf` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `conta` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `valor` float(10,2) UNSIGNED ZEROFILL NOT NULL,
  `transacoes` int(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
)

Could someone help me?

Mysql Version: 5.7.23 - Mysql Community Server

http://sqlfiddle.com/#! 9/73f38f/2

Answer 1

CREATE TABLE IF NOT EXISTS `campanha` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ano_mes` date DEFAULT NULL,
  `transacoes` int(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

INSERT INTO campanha (ano_mes, transacoes) VALUES ('2019-09-01', 45561);
INSERT INTO campanha (ano_mes, transacoes) VALUES ('2019-10-01', 48598);

SELECT mes, transacoes, evolucao FROM (
  SELECT 
    mes, 
    transacoes,
    IFNULL(transacoes * 100 / @lastValue - 100, 0) AS evolucao,
    @lastValue := transacoes
  FROM (
    SELECT MONTH (ano_mes) AS mes, SUM(transacoes) AS transacoes FROM campanha,
    (SELECT @lastValue := 0) SQLVars GROUP BY mes) meio
) final;

Output:

+------+------------+----------+
| mes  | transacoes | evolucao |
+------+------------+----------+
|    9 |      45561 |   0.0000 |
|   10 |      48598 |   6.6658 |
+------+------------+----------+