Use rank() en count() in same SQL query

Question

Shouldn't be very difficult but I won't get to it so quickly.

I want to select all customers, a count() of the number the orders per customer and a dense_rank() based on this count.

I've tried the query below. But this gives all rows the value "1" as RankNumber.

select *, Dense_RANK() over(partition by NrOrders order by NrOrders desc) as RankNumber
from(
select CustomerID, count(*) as NrOrders
from Orders
group by CustomerID
) cpc

So the desired result would be.

CustomerID  NrOrders  RankNumber
1           6         1
2           3         2
3           3         2
4           2         3

Answer 1

The problem with your code is the partition clause of the window function. It puts customers in different groups depending on their order count, while you want all rows in the same partition, ordered by descending order count.

Also, there is no need for a subquery, you can use the window function directly in the aggregate query.

Consider:

select 
    CustomerID,
    count(*) as NrOrders,
    dense_rank() over(order by count(*) desc) rn
from Orders
group by CustomerID
order by NrOrders