Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Calculate CARG of the top 5 makers in SQL

So I’m solving a question of a project, and I need to calculate the Compound Annual Growth Rate (CAGR), together with the total sales of the top 5 EV makers from fiscal year 2022 to 2024.

I found out the query to calculate the sales of the top five 5 makers from FY2022 to FY2024 using a query like this.

SELECT 
    maker, 
    SUM(electric_vehicles_sold) AS total_ev_sold
    
    FROM new_schema.sales_by_makers
    WHERE fiscal_year BETWEEN 2022 AND 2024 AND vehicle_category = '4-Wheelers'
    GROUP BY maker
    ORDER BY total_ev_sold DESC
    LIMIT 5;

I also got the result as expected. output

Now adding the column for CAGR is the real problem, it should be something like this.

SELECT 
    maker, 
    SUM(electric_vehicles_sold) AS total_ev_sold,
    ((total_ev_sold / starting_point) ^1/2) - 1 AS CAGR
    
    FROM new_schema.sales_by_makers
    WHERE fiscal_year BETWEEN 2022 AND 2024 AND vehicle_category = '4-Wheelers'
    GROUP BY maker
    ORDER BY total_ev_sold DESC
    LIMIT 5;

Unfortunately, I have no idea on how to get the starting_point, which should be the total_ev_sold of fiscal year 2022. I’ve read many articles about calculating CAGR in SQL but none of them are appropriate in my case. Please give some help! Feel free to ask questions if you need more information.

Tags:

2

Answers


  1. 0

    Using CTE approach should give you the desired result.

    WITH sales_data AS (
    SELECT maker, fiscal_year, SUM(electric_vehicles_sold) AS total_ev_sold
    FROM new_schema.sales_by_makers
    WHERE fiscal_year BETWEEN 2022 AND 2024 
    AND vehicle_category = '4-Wheelers'
    GROUP BY maker, fiscal_year
),
start_sales AS (
    SELECT maker, total_ev_sold AS start_ev_sold
    FROM sales_data
    WHERE fiscal_year = 2022
),
end_sales AS (
    SELECT maker, total_ev_sold AS end_ev_sold
    FROM sales_data
    WHERE fiscal_year = 2024
)

SELECT sd.maker, SUM(sd.total_ev_sold) AS total_ev_sold, ((es.end_ev_sold / ss.start_ev_sold) ^ (1.0 / 2)) - 1 AS CAGR
FROM sales_data sd
JOIN start_sales ss ON sd.maker = ss.maker
JOIN end_sales es ON sd.maker = es.maker
GROUP BY sd.maker, ss.start_ev_sold, es.end_ev_sold
ORDER BY total_ev_sold DESC
LIMIT 5;
    Login or Signup to reply.
  2. 0

    See example

    select maker,total_sold
  ,sale2022,sale2024
  ,(sale2024 / sale2022) r22y24
  ,power((sale2024 / sale2022),  (1.0 / 2.0)) - 1 AS CAGR
from(
  select maker
    ,sum(case when fiscal_year=2022 then electric_vehicles_sold else 0 end) sale2022
    ,sum(case when fiscal_year=2024 then electric_vehicles_sold else 0 end) sale2024
    ,sum(electric_vehicles_sold) total_sold
  from sales_by_makers s
  where fiscal_year between 2022 and 2024
  group by maker
  order by total_sold desc
  limit 5
)a
order by total_sold desc
    maker total_sold sale2022 sale2024 r22y24 CAGR
    BYD 1906365 714887 1191478 1.6667 0.29099426799657024
    Tesla 1063513 437917 625596 1.4286 0.19522888226481538
    BMW 403283 191029 212254 1.1111 0.05409144954315992
    Wuling 359334 179667 179667 1.0000 0
    Volkswagen Group 345745 188588 157157 0.8333 -0.08712810318205111

    fiddle

    All values in example completely made up for example.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search