How to Calculate Percentage Growth Week Over Week in MySQL

Last updated on May 17th, 2020 at 06:26 am

Monitoring Percentage Growth Week-over-week is a useful way to track weekly growth trends in your business. Here’s how to calculate percentage growth week over week in MySQL. It can be used to calculate week over week change in SQL for any metric.

How to Calculate Percentage Growth Week Over Week in MySQL

Here are the steps to calculate percentage growth week over week in MySQL. Let’s say you have a table weekly_sales(week,sale) that contains weekly sales numbers, as shown below.

mysql> create table weekly_sales(week int, sale int);

mysql> insert into weekly_sales(week,sale) 
       values(1,20),(2,30),(3,25),(4,45),(5,25);

mysql> select * from weekly_sales;
+------+------+
| week | sale |
+------+------+
|    1 |   20 |
|    2 |   30 |
|    3 |   25 |
|    4 |   45 |
|    5 |   25 |
+------+------+

You can calculate percentage growth week over week using the following SQL query for growth percentage.

mysql> select week, sale,
         if(@last_entry = 0, 0, round(((sale - @last_entry) / @last_entry) * 100,2)) "growth rate",
         @last_entry := sale
         from
         (select @last_entry := 0) x,
         (select week, sum(sale) sale
         from   weekly_sales
         group by week) y;
+------+------+-------------+---------------------+
| week | sale | growth rate | @last_entry := sale |
+------+------+-------------+---------------------+
|    1 |   20 |           0 |                  20 |
|    2 |   30 |       50.00 |                  30 |
|    3 |   25 |      -16.67 |                  25 |
|    4 |   45 |       80.00 |                  45 |
|    5 |   25 |      -44.44 |                  25 |
+------+------+-------------+---------------------+

In the above query, for each week, we store its previous week’s data in a temporary variable called last_entry and use it to calculate percentage growth.

Bonus Read : How to Calculate Percentage of Two Columns in MySQL

However, in most cases, you will have daily sales data instead of weekly sales. So, in that case, you need to first aggregate it to weekly sales and then use the above query to calculate percentage growth week over week.

Let’s say you have a daily sales data table sales(order_date, sale)

mysql>create table sales(order_date date,sale int);

mysql>insert into sales(order_date,sale)
values('2020-01-01',10),('2020-01-02',12),('2020-01-03',15),
('2020-01-04',11),('2020-01-05',13),('2020-01-06',9),
('2020-01-07',21),('2020-01-08',10),('2020-01-09',10),
('2020-01-10',2),('2020-01-11',16),('2020-01-12',12),
('2020-01-13',10),('2020-01-14',18),('2020-01-15',15),
('2020-01-16',12),('2020-01-17',10),('2020-01-18',18),
('2020-01-19',14),('2020-01-20',16),('2020-01-21',12),
('2020-01-22',21),('2020-01-23',13),('2020-01-24',15),
('2020-01-25',20),('2020-01-26',14),('2020-01-27',16),
('2020-01-28',15),('2020-01-29',10),('2020-01-30',18);

mysql>select * from sales;
+------------+------+
| order_date | sale |
+------------+------+
| 2020-01-01 |   10 |
| 2020-01-02 |   12 |
| 2020-01-03 |   15 |
| 2020-01-04 |   11 |
| 2020-01-05 |   13 |
| 2020-01-06 |    9 |
| 2020-01-07 |   21 |
| 2020-01-08 |   10 |
| 2020-01-09 |   10 |
| ...        |   ...|
+------------+------+

Let’s say you want to calculate percentage growth week over week, here’s the SQL for percent change over time.

mysql> select week, sale,
            if(@last_entry = 0, 0, round(((sale - @last_entry) / @last_entry) * 100,2)) "growth rate",
            @last_entry := sale
     from
           (select @last_entry := 0) x,
           (select week, sum(sale) sale
            from   (select week(order_date) as week,sum(sale) as sale
                    from sales group by week(order_date)) weekly_sales
            group by week) y;
+------+------+-------------+---------------------+
| week | sale | growth rate | @last_entry := sale |
+------+------+-------------+---------------------+
|    0 |   48 |           0 |                  48 |
|    1 |   81 |       68.75 |                  81 |
|    2 |   95 |       17.28 |                  95 |
|    3 |  111 |       16.84 |                 111 |
|    4 |   73 |      -34.23 |                  73 |
+------+------+-------------+---------------------+

In the above query, we first aggregate daily sales into weekly sales (in bold)and then use the earlier query to calculate percentage growth week over week.

Bonus Read : How to Calculate Percentage of One Column in MySQL

If you want to filter the data used for your query, you can add a WHERE clause in your query as shown below

mysql> select week, sale,
            if(@last_entry = 0, 0, round(((sale - @last_entry) / @last_entry) * 100,2)) "growth rate",
            @last_entry := sale
     from
           (select @last_entry := 0) x,
           (select week, sum(sale) sale
            from   (select week(order_date) as week,sum(sale) as sale
                    from sales 
                    WHERE condition
                    group by week(order_date)) weekly_sales
            group by week) y;

Please note, since the calculation of percentage growth week over week depends on storing previous row’s value in a temporary variable, please make sure that your table is already sorted in ascending order of week numbers. Else it might give wrong results.

After you calculate percentage growth week over week, you can use a charting tool to plot the result in a bar chart, and share it with your team. Here’s an example of bar chart that shows percent change over time, created using Ubiq.