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.

Answer 1

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

Answer 2

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