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

Mysql – List the states with negative penetration (decline) in EV sales from 2022 to 2024

I’m working on a project and I need to answer a question with the same context as the question title.

To be honest, I’m not sure if I understand the question correctly, but here is my approach so far.

Firstly, I calculated the penetration rate in fiscal year 2022 like this.

SELECT state, (SUM(electric_vehicles_sold)) / (SUM(total_vehicles_sold)) * 100 AS penetration_rate, fiscal_year
    FROM sales_by_states
    WHERE fiscal_year = 2022
    GROUP BY state

I did the same thing to calculate fiscal year 2024, just change the WHERE clause to WHERE fiscal_year = 2024 and received the right result.

Here is the brief image of the output.
output

Now, I planned to do something like [PR 2024] – [PR 2022] and if the result is < 0 then the state has declining rate. However, I have no idea on how to do that…

I will be delighted with any help! Feel free to ask me any questions if you need more information.

Tags:

2

Answers


  1. 0 
    with FY_2022 as (
SELECT 
    state, 
    (SUM(electric_vehicles_sold)) / (SUM(total_vehicles_sold)) * 100 AS penetration_rate, 
    fiscal_year
FROM sales_by_states
    WHERE fiscal_year = 2022
    GROUP BY state
), FY_2024 as (
SELECT 
    state, 
    (SUM(electric_vehicles_sold)) / (SUM(total_vehicles_sold)) * 100 AS penetration_rate, 
    fiscal_year
FROM sales_by_states
    WHERE fiscal_year = 2024
    GROUP BY state
)
SELECT 
    COALESCE(FY_2022.STATE,FY_2024.STATE) AS STATE,
    FY_2022.PENETRATION_RATE AS FY_2022_PENETRATION_RATE,
    FY_2024.PENETRATION_RATE AS FY_2024_PENETRATION_RATE,
    CASE WHEN (FY_2024.PENETRATION_RATE - FY_2022.PENETRATION_RATE) < 0 THEN 'DECINING RATE' ELSE 'INCREASING RATE' END AS CHANGE_RATE
FROM FY_2022 FULL OUTER JOIN FY_2024 ON FY_2022.STATE = FY_2024.STATE ;
    Login or Signup to reply.
  2. 0

    Using analytic functions you can get the data from previous fiscal year in every row.

    WITH    -- S a m p l e    D a t a :
  sales_by_states (id, state, sold, fiscal_year) AS
    ( Select 1, 'State A', 100, 2022 Union All 
      Select 2, 'State B', 200, 2022 Union All 
      Select 3, 'State C', 300, 2022 Union All 
      --
      Select 4, 'State A', 105, 2023 Union All 
      Select 5, 'State B', 195, 2023 Union All 
      Select 6, 'State C', 300, 2023 Union All
      --
      Select 7, 'State A', 102, 2024 Union All 
      Select 8, 'State B', 200, 2024 Union All 
      Select 9, 'State C', 285, 2024 
  )

    --      S Q L : 
SELECT    g.id, g.state, g.sold, g.fiscal_year, 
          g.sold_prev_year, g.sold_diff, g.pct,
          g.sold_prev_year * 100 / Sum(g.sold_prev_year) 
                                     Over(Partition By g.fiscal_year) as pct_prev_year, 
          g.pct - 
          g.sold_prev_year * 100 / Sum(g.sold_prev_year) 
                                     Over(Partition By g.fiscal_year) as pct_diff
FROM
  ( Select    id, state, sold, fiscal_year, 
              --
              LAG(sold) Over(Partition By state Order By fiscal_year) as sold_prev_year, 
              Case When LAG(sold) Over(Partition By state Order By fiscal_year) Is Not Null
                   Then sold - LAG(sold) Over(Partition By state Order By fiscal_year)
              End sold_diff, 
              --
              sold * 100 / Sum(sold) Over(Partition By fiscal_year) as pct
    From      sales_by_states
  ) g
ORDER BY g.id

    /*      R e s u l t :
id  state     sold  fiscal_year sold_prev_year   sold_diff        pct   pct_prev_year     pct_diff
--  -------  -----  ----------  --------------  ----------  ---------  --------------  -----------  
1   State A    100        2022            null        null    16.6667            null         null
2   State B    200        2022            null        null    33.3333            null         null
3   State C    300        2022            null        null    50.0000            null         null
4   State A    105        2023             100           5    17.5000         16.6667       0.8333
5   State B    195        2023             200          -5    32.5000         33.3333      -0.8333
6   State C    300        2023             300           0    50.0000         50.0000       0.0000
7   State A    102        2024             105          -3    17.3765         17.5000      -0.1235
8   State B    200        2024             195           5    34.0716         32.5000       1.5716
9   State C    285        2024             300         -15    48.5520         50.0000      -1.4480      */

    Now you have in the same row the data from actual and previous year along with the sold and percentage difference.
    From this resultset you can create different reports including the one from your question.
    If you add a condition to filter just the rows having negative sold difference to the outer query:

    ...
WHERE     g.sold_diff < 0
...

    … then …

    /*        R e s u l t :
id  state     sold  fiscal_year sold_prev_year   sold_diff        pct   pct_prev_year     pct_diff
--  -------  -----  ----------  --------------  ----------  ---------  --------------  ----------- 
5   State B    195        2023             200          -5    32.5000         33.3333      -0.8333
7   State A    102        2024             105          -3    17.3765         17.5000      -0.1235
9   State C    285        2024             300         -15    48.5520         50.0000      -1.4480      */
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search