Trying to utilize a window function instead of this script-CodePudding

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

CodePudding user response：

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;