CODEWITHSUNDEEP
sqlpostgresqlrankdense-ranknorthwind
jordan_butler_33
jordan_butler_33

Reputation: 31

RANK vs DENSE_RANK for postgreSQL queries

I just started learning SQL about a month ago and have been practicing writing queries from the NorthWind sample database on postreSQL in pgAdmin4. After posting about this specific query I am trying to solve the other day, this is what I have come up with:

-- Return Month, Product name, SalesForMonth, SalesForMonthRanking for nth best selling product 
-- for each month in 1997

This is the query I wrote in order to solve said prompt:

SELECT * 
FROM (
    SELECT 
        CAST( EXTRACT( MONTH FROM o.orderdate) AS integer) AS month, 
        p.productname,
        ROUND (CAST (SUM (od.quantity * od.unitprice) AS numeric),2) salesmonth,
        RANK () OVER (PARTITION BY EXTRACT( MONTH FROM o.orderdate)
        ORDER BY SUM (od.quantity * od.unitprice) DESC) monthrank
    FROM order_details od
    INNER JOIN products p ON od.productid = p.productid
    INNER JOIN orders o ON od.orderid = o.orderid
    WHERE o.orderdate >= '1997-01-01' AND o.orderdate <= '1997-12-31'
    GROUP BY EXTRACT( MONTH FROM o.orderdate), p.productname 
) subq
WHERE monthrank = n 
ORDER BY month, monthrank, productname

While the result is correct, I noticed that when finding the 27th highest selling product for each month there is no result for month = 6 because there are 2 products tied for the 26th highest selling product. My question is: is this supposed to happen or should I change my RANK statements to DENSE_RANK statements to combat this issue. Also if there is any advice anyone wanted to give me on the actual structure of my query that would be greatly appreciated.

Upvotes: 1

Views: 92

Answers (0)

Related Questions