Vryjdfkk

I have a challenge I set out to do that seemed initially trivial. Not so for my developper brain.

Consider the following simple view, used to validate a cron I wrote that updates 200 000 customer account statements every saturday.

It goes as follows:

mysql> SELECT

    ->     DATE_FORMAT(s.created, "%Y-%m-%d") as "Date",

    ->     count(s.id) AS "Accounts credited",

    ->     sum(s.withdrawal) "Total Credited"

    ->   --  100 * (sum(s.withdrawal) - sum(prev.withdrawal)) 

         --   / sum(prev.withdrawal) "Difference in %"

    ->     FROM statements s

    -> --    LEFT JOIN prev

    -> --        s.created - interval 7 DAY

    ->  --     ON prev.created = s.created - interval 7 DAY 

        --     AND (prev.status_id = 'OPEN' 

        --     OR prev.status_id = 'PENDING')

    ->     WHERE (s.status_id = 'OPEN' OR  s.status_id = 'PENDING')

    ->     GROUP BY YEAR(s.created), MONTH(s.created), DAY(s.created)

    ->       ORDER BY s.created DESC

    ->     LIMIT 12;



+------------+-------------------+----------------+

| Date       | Accounts credited | Total Credited |

+------------+-------------------+----------------+

| 2019-01-19 |             18175 |      323173.68 |

| 2019-01-12 |             18135 |      324768.43 |

| 2019-01-10 |                80 |        1497.75 |

| 2019-01-09 |                51 |         933.50 |

| 2019-01-08 |                10 |         187.50 |

| 2019-01-05 |             17588 |      316968.49 |

| 2018-12-29 |             17893 |      325404.18 |

| 2018-12-28 |                 1 |          35.00 |

| 2018-12-22 |             17353 |      317048.18 |

| 2018-12-15 |             16893 |      310181.34 |

| 2018-12-08 |             16220 |      299547.09 |

| 2018-12-01 |             15476 |      287699.59 |

+------------+-------------------+----------------+

12 rows in set (0.79 sec)

As is, the query is efficient and practical. I merely would like to add a column, difference in percentage, from previous week's total, as seen with the -- commented out code.

I have tried various approaches, but because of the GROUP BY, adding an inline column to get the sum(withdrawal) of previous week makes the query run ... forever.

I then tried the LEFT JOIN approach, but this has the same problem, Obviously. I think the added JOIN has to fetch the sum of previous week for every row of the outer select.

I then had the (not so smart) idea of querying my view, even but then it seems I would have the same issue.

I assume there are much more optimal approaches out there to this simple task.

Is there an elegant way to calculate a percentage from such a query?

Would a stored procedure or some other 'non-plain-sql' approach be more optimal?