Mysql Calculate Growth based on Quarters

  • A+
Category:Languages

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.

 


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

Comment

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: