Mysql Calculate Growth based on Quarters

Question

I have a database with two tables - companies and reports. I want to calculate the change from q1 (quarter 1) to q2 (quarter 2). I have tried to use the (following) sub-query, but then the main query fails...

FROM
    (SELECT revenue FROM reports WHERE quarter = 'q2' AND fiscal_year = 2018) AS q,
    (SELECT revenue FROM reports WHERE quarter = 'q1' AND fiscal_year = 2017) AS lq

Here is DB Fiddle to help you understand the problem and schema:

https://www.db-fiddle.com/f/eE8SNRojn45h7Rc1rPCEVN/4

Current Simple Query.

SELECT 
    c.name, r.quarter, r.fiscal_year, r.revenue, r.taxes, r.employees
FROM 
    companies c
JOIN
    reports r 
ON
    r.company_id = c.id
WHERE
    c.is_marked = 1;

Expected Results (this is what i need):

+---------+----------+----------------+----------+--------------+-----------+------------------+
|  Name   | Revenue  | Revenue_change |  Taxes   | Taxes_change | Employees | Employees_change |
+---------+----------+----------------+----------+--------------+-----------+------------------+
| ABC INC |    11056 | +54.77         | 35000.86 | -28.57%      |       568 | -32              |
| XYZ INC |     5000 | null           | null     | null         |        10 | +5               |
+---------+----------+----------------+----------+--------------+-----------+------------------+

I would really appreciate your help to build this query. Thanks in advance.

I am also looking for your suggestion about better approach to achieve this - Is MySQL Schema correct for this calculation? Should I achieve the result with programming? — seoppc, Oct 27 '18 at 21:23
If you combine a simple query with for example javascript you can make the result avaliable faster. I have done that using a ”large” dataset (2-5 GB). I was surprised to see total processing time go from approx 4000ms to approx 40 ms. 2 orders of magnitude by adding 20-30 lines of program code. If you have a similar setting I suggest that you to try it and compare processing times. — Gillsoft AB, Oct 29 '18 at 20:53
As you are already fetching and displaying the revenue from Q2, and presumably using your framework to do so.. it would probably be easier, and arguably more readable, to do the same for Q1 and just perform this comparison at the application level. — Arth, Oct 30 '18 at 16:37
@Arth So You mean - I should get two separated records based on given quarters and then compare them using application logic? — seoppc, Oct 30 '18 at 18:08
What is your MySQL server version ? Depending on it, complexity of solution may change. Is there any chance you can upgrade to latest ? — Madhur Bhaiya, Nov 03 '18 at 05:08
@seoppc, I had experienced like that. Now, what is the calculation formula you want to use for change from q1 (quarter 1) to q2 (quarter 2)? — er.irfankhan11, Nov 05 '18 at 12:35

Lukasz Szozda · Answer 1 · 2018-10-30T16:31:46.020

6

Using MySQL 8.0 windowed functions:

WITH cte AS (
  SELECT c.name, quarter, fiscal_year
   ,revenue,100*(revenue-LAG(revenue) OVER s)/NULLIF(revenue,0) AS change_revenue
   ,taxes,100*(taxes-LAG(taxes) OVER s)/NULLIF(taxes,0) AS change_taxes
   ,employees,employees-LAG(employees) OVER s AS change_employees
  FROM companies c
  JOIN reports r ON r.company_id = c.id
  WINDOW s AS (PARTITION BY r.company_id ORDER BY fiscal_year, quarter)
)
SELECT *
FROM cte
WHERE quarter = 'Q2';  -- only to get specific quarter
-- comment this condition to get quarter to quarter comparison

db<>fiddle demo

edited Oct 30 '18 at 16:31

answered Oct 29 '18 at 21:04

Lukasz Szozda

162,964
23
234
275

1

Looks cool. Does it work on Mysql 5.7.23? Can it also compare Q2 2018 to Q2 2017? – seoppc Oct 30 '18 at 18:05
@seoppc Only MySQL 8.0 - added support for [windowed function](https://stackoverflow.com/questions/1895110/row-number-in-mysql/46753800#46753800). And yes, you could compare year by year, just change window definition to `WINDOW s AS (PARTITION BY r.company_id, quarter ORDER BY fiscal_year)` – Lukasz Szozda Oct 30 '18 at 20:31

score 3 · Answer 2 · answered Oct 29 '18 at 20:16

3

You can do it in plain SQL if you need to compare just two quarters. No programming needed.
There are no subqueries, just join companies to reports twice based on quarter.

select 
    c.name, 
    r2.revenue, 
    100 * (r2.revenue - r1.revenue) / r2.revenue as revenue_change,
    r2.taxes, 
    100 * (r2.taxes - r1.taxes) / r2.taxes as taxes_change,
    r2.employees, 
    r2.employees - r1.employees as employees_change
from 
    companies c
    LEFT JOIN reports r1 ON (c.id = r1.company_id and r1.quarter = 'q1')
    LEFT JOIN reports r2 ON (c.id = r2.company_id and r2.quarter = 'q2')

See https://www.db-fiddle.com/f/6hwbPofSwAiqGBPFZWKxhi/0

answered Oct 29 '18 at 20:16

mike

5,047
2
26
32

This is by far the simplest and most readable database solution given the schema.. just a quick note, for percentage change from Quarter 1, `r1.revenue` and `r1.taxes` should be the denominators for `revenue_change` and `taxes_change` respectively (not r2) – Arth Oct 30 '18 at 16:29
@Arth Well what if you want to compare q2 to q1, q3 to q2, q4 to q3 on single query? Hardcoding values is not simplest solution. – Lukasz Szozda Oct 30 '18 at 16:33
@LukaszSzozda Well then you are changing the question! With this query you could easily change a bound in value for each of the quarters ('q1' and 'q2' in this example), and compare any two quarters. – Arth Oct 30 '18 at 16:41
@seoppc since your questions asked only to compare two quarters in a single query, this is the recommended approach. If you need to do a comparison of multiple quarters, it may not be the most efficient solution, but that's a different question ;) – mike Oct 30 '18 at 20:04
Is it not possible to join based on `(c.id = r1.company_id and r1.quarter = 'q1' and r1.fiscal_year = 2017) ` – seoppc Oct 31 '18 at 10:58
Thanks year was always part of question, that's why there fiscal_year as column. – seoppc Oct 31 '18 at 11:58

score 2 · Answer 3 · answered Oct 27 '18 at 20:56

It is very difficult to get result using pure SQL. But I make it.

Execute following sql. I hope you get help with this SQL.

select 
    qd2.name as Name,
    qd2.Revenue as Revenue,
    qd2.Revenue - qd1.Revenue as Revenue_Change,
    qd2.Taxes as Taxes,
    (qd2.Taxes - qd1.Taxes) * 100 / qd1.Taxes as Taxes_Change,
    qd2.Employees as Employees,
    (qd2.Employees - qd1.Employees) as Employees_Change
from 
    (
        SELECT 
            (@cnt := @cnt + 1) AS rowNumber, 
            c.name as name, 
            r.revenue as Revenue,
            r.taxes as Taxes,
            r.employees as Employees
        FROM 
            companies c
        JOIN
            reports r 
        CROSS JOIN (SELECT @cnt := 0) AS dummy
        ON
            r.company_id = c.id and
            r.quarter = "q2"
        order by name
    ) as qd2
    JOIN
    (
        SELECT 
            (@cnt2 := @cnt2 + 1) AS rowNumber, 
            c.name as name, 
            r.revenue as Revenue,
            r.taxes as Taxes,
            r.employees as Employees
        FROM 
            companies c
        JOIN
            reports r 
        CROSS JOIN (SELECT @cnt2 := 0) AS dummy
        ON
            r.company_id = c.id and
            r.quarter = "q1"
        order by name
    ) as qd1
    ON qd1.rowNumber = qd2.rowNumber

Results are as following

Name    Revenue Taxes   Employees   Revenue_Change  Taxes_Change    Employees_Change
ABC INC 11056   35000.86    568 6056    -22.221798  -32
XYZ LLC 5000    null    10  null    null    5

Do you suggest to use programming instead of sql query? – seoppc Oct 27 '18 at 21:17 — seoppc, Oct 27 '18 at 21:17

Shidersz · Answer 4 · 2018-10-31T16:20:14.757

I let you my approach to this, using only one join with group by clause and aggregate functions. The final step of pecentajes calculation is doing using a derived table from the first approach:

SELECT
    der.name, der.quarter, der.fiscal_year,
    der.revenue,
    100 * der.revenue_change / der.revenue AS revenue_change,
    der.taxes,
    100 * der.taxes_change / der.taxes AS taxes_change,
    der.employees,
    der.employees_change
FROM
(
    /* First approach (with raw changes) */
    SELECT
        c.name,
        MAX(r.quarter) AS quarter,
        ANY_VALUE(r.fiscal_year) AS fiscal_year,
        SUM(CASE WHEN r.quarter = "Q2" THEN r.revenue END) AS revenue,
        IF (COUNT(r.revenue) = 2,
            SUM(CASE WHEN r.quarter = "Q1" THEN (-1 * r.revenue) ELSE r.revenue END),
            NULL) AS revenue_change,
        SUM(CASE WHEN r.quarter = "Q2" THEN r.taxes END) AS taxes,
        IF (COUNT(r.taxes) = 2,
            SUM(CASE WHEN r.quarter = "Q1" THEN (-1 * r.taxes) ELSE r.taxes END),
            NULL) AS taxes_change,
        SUM(CASE WHEN r.quarter = "Q2" THEN r.employees END) AS employees,
        IF (COUNT(r.employees) = 2,
            SUM(CASE WHEN r.quarter = "Q1" THEN (-1 * r.employees) ELSE r.employees END),
            NULL) AS employees_change
    FROM
        companies AS c
    LEFT JOIN
        reports AS r ON r.company_id = c.id AND r.quarter IN ("Q1","Q2")
    GROUP BY
        c.id
) AS der

You can check the progression to the final query in next link:

https://www.db-fiddle.com/f/2tUC1gGJusVnXSyGhHGWc/3

Generalizing the Query

I generalized the previous query for approach the comparison of quarters from different fiscal year, as you asked on the commentaries, this is how I will do it:

SET @q1Year = 2017;
SET @q2Year = 2018;
SET @q1 = "Q1" COLLATE utf8mb4_unicode_ci;
SET @q2 = "Q2" COLLATE utf8mb4_unicode_ci;

SELECT
    der.name,
    der.compared_quarters,
    der.quarter,
    der.fiscal_year,
    der.revenue,
    100 * der.revenue_change / der.revenue AS revenue_change,
    der.taxes,
    100 * der.taxes_change / der.taxes AS taxes_change,
    der.employees,
    der.employees_change
FROM                                                       
(
    /* This query generate raw changes on revenue, taxes and employees */
    SELECT
        c.name,
        CONCAT(@q1,"-",@q1Year," vs ",@q2,"-",@q2Year) AS compared_quarters,
        @q2 AS quarter,
        @q2Year AS fiscal_year,
        SUM(IF(r.quarter = @q2, r.revenue, 0)) AS revenue,
        IF (COUNT(r.revenue) = 2,
            SUM(IF(r.quarter = @q1, -1 * r.revenue, r.revenue)),
            NULL) AS revenue_change,
        SUM(IF(r.quarter = @q2, r.taxes, 0)) AS taxes,
        IF (COUNT(r.taxes) = 2,
            SUM(IF(r.quarter = @q1, -1 * r.taxes, r.taxes)),
            NULL) AS taxes_change,
        SUM(IF(r.quarter = @q2, r.employees, 0)) AS employees,
        IF (COUNT(r.employees) = 2,
            SUM(IF(r.quarter = @q1, -1 * r.employees, r.employees)),
            NULL) AS employees_change
    FROM
        companies AS c
    LEFT JOIN
        reports AS r ON r.company_id = c.id
    AND
        ((r.quarter = @q1 AND r.fiscal_year = @q1Year) OR (r.quarter = @q2 AND r.fiscal_year = @q2Year))
    GROUP BY
        c.id
) AS der;

You can check an example on next link:

https://www.db-fiddle.com/f/2tUC1gGJusVnXSyGhHGWc/4

That was not part of your first question, but if i'm not wrong, in this particular case you can modify the **left join** restriction like this: `LEFT JOIN reports AS r ON r.company_id = c.id AND ( (r.quarter = "Q1" AND r.fiscal_year = "2017") OR (r.quarter = "Q2" AND r.fiscal_year = "2018"))`. I'm working now, but i will test this later. — Shidersz, Oct 30 '18 at 18:16
Thanks year was always part of question, that's why there fiscal_year as column. — seoppc, Oct 31 '18 at 11:58
Ok, I based my query on your expected results, that is why I don't think year was part of the problem, I will try to made a more generic query when I get some time. Anyway, you have better answers there, I'm just trying a different approach in relation to those answers. — Shidersz, Oct 31 '18 at 14:58

score 0 · Answer 5 · answered Oct 31 '18 at 22:08

I gave it a try. This is how I would do it.

SELECT 
  c.name, 
  reportsQ2.quarter, 
  reportsQ2.revenue as revenu1, 
  reportsQ1.revenue as revenue2, 
  (COALESCE(reportsQ2.revenue, 0) - COALESCE(reportsQ1.revenue, 0)) as difference
FROM 
    reports as reportsQ2 
LEFT JOIN 
    reports as reportsQ1 ON reportsQ1.company_id = reportsQ2.company_id
AND 
    reportsQ1.quarter = 'Q1'
LEFT JOIN
    companies as c ON c.id = reportsQ2.company_id
WHERE 
    reportsQ2.quarter = 'Q2'

So I basicly selected all reports from the second quarter at first. Then I joined the first quarter with the same company id.

At this point, you already have all necessary informations to proceed to the calculations. I used COALESCE() to make sure that null values don't interfere with the calculations to output null instead of a valid number.

Finally, I joined the companies table to get the names.

I did the maths with the revenue rows and output them as difference.

Hope that helps!

score 0 · Answer 6 · edited Nov 05 '18 at 14:08

It gave me the output what you need .

select 
    c.name, 
    r2.revenue, 
     concat(TRUNCATE(100 * (r2.revenue - r1.revenue) / r2.revenue,2),"%") as revenue_change,
    r2.taxes, 
   concat(TRUNCATE( 100 * (r2.taxes - r1.taxes) / r2.taxes,2),"%")  as taxes_change,
    r2.employees, 
    r2.employees - r1.employees as employees_change
from 
    companies c
    LEFT JOIN reports r1 ON (c.id = r1.company_id and r1.quarter = 'q1') 
    LEFT JOIN reports r2 ON (c.id = r2.company_id and r2.quarter = 'q2')

Output:

Thanks!!!

Mysql Calculate Growth based on Quarters

6 Answers6