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.
2
Answers
Using analytic functions you can get the data from previous fiscal year in every row.
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:
… then …