Trying to utilize a window function instead of this script

I’m trying to improve my query for this topic at hand. I’m trying to find the top 5 and bottom 5 growth rates per state from 2020 to 2021 in my org. The table has the columns as specified: orderid, orderdate, totaldue, state, etc. (these are probably the most important columns). This is the query I created so far, while it works I think it would be more efficient if I was able to implement a window function instead.

SELECT state, SUM(TotalDue) as sum
into #temp2020
from table
where OrderDate like "2020%"
group by StateProvince
order by sum desc;

SELECT state, SUM(TotalDue) as sum
into #temp2021
from table
where OrderDate like "2021%"
group by StateProvince
order by sum desc;

--top 5 growth rates--
select #temp2020.state, ((#temp2021.sum-#temp2020.sum)/#temp2020.sum) as 'growthrate'
from #temp2020
join #temp2021 on #temp2021.state = #temp2020.state
order by growthrate desc limit 5

--bottom 5 growth rates--
select #temp2020.state, ((#temp2021.sum-#temp2020.sum)/#temp2020.sum) as 'growthrate'
from #temp2020
join #temp2021 on #temp2021.state = #temp2020.state
order by growthrate asc limit 5

drop table if exists #temp2020
drop table if exists #temp2021

Answer

You could use DENSE_RANK here:

WITH cte AS (
    SELECT state, SUM(TotalDue) AS sum,
           DENSE_RANK() OVER (ORDER BY SUM(TotalDue)) rnk_asc,
           DENSE_RANK() OVER (ORDER BY SUM(TotalDue) DESC) rnk_desc
    FROM yourTable
    WHERE YEAR(OrderDate) IN (2020, 2021)
    GROUP BY state
)

SELECT state, sum
FROM cte
WHERE rnk_asc <= 5 OR rnk_desc <= 5
ORDER BY state, sum;