Mysql Calculate Growth based on Quarters

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

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

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

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

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!

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!!!