RANK, DENSE_RANK AND ROW_NUMBER analytic functions

I wrote this test to better understand the use of RANK, DENSE_RANK and ROW_NUMBER Oracle analytic functions and Top-N querying. The beauty of this exercise is that all 3 analytic functions are illustrated with one example.


Suppose the business requirement in this case is to rank the products of each customer by the quantity purchased.

The test table I used for this is:

CREATE TABLE t
(
customer_name VARCHAR2(50),
product_name VARCHAR2(50),
hits NUMBER
)

The data in the table is 16 rows like below:

INSERT INTO t VALUES('bill', 'egg', 1);
INSERT INTO t VALUES('bill', 'egg', 1);
INSERT INTO t VALUES('bill', 'beer', 3);
INSERT INTO t VALUES('bill', 'beer', 2);
INSERT INTO t VALUES('bill', 'jeans', 1);
INSERT INTO t VALUES('larry', 'beer', 4);
INSERT INTO t VALUES('larry', 'beer', 2);
INSERT INTO t VALUES('larry', 'cheese', 1);
INSERT INTO t VALUES('larry', 'cheese', 1);
INSERT INTO t VALUES('larry', 'cheese', 1);
INSERT INTO t VALUES('larry', 'cheese', 1);
INSERT INTO t VALUES('larry', 'cheese', 1);
INSERT INTO t VALUES('larry', 'cheese', 1);
INSERT INTO t VALUES('larry', 'olives', 1);
INSERT INTO t VALUES('larry', 'olives', 1);
INSERT INTO t VALUES('larry', 'car', 1);
COMMIT;

SELECT * FROM t;

CUSTOMER_NAME      PRODUCT_NAME            HITS
------------------ ----------------- ----------
bill               egg                        1
bill               egg                        1
bill               beer                       3
bill               beer                       2
bill               jeans                      1
larry              beer                       4
larry              beer                       2
larry              cheese                     1
larry              cheese                     1
larry              cheese                     1
larry              cheese                     1
larry              cheese                     1
larry              cheese                     1
larry              olives                     1
larry              olives                     1
larry              car                        1

The solution query

SELECT
customer_name,
product_name,
hits,
rank() over(PARTITION BY customer_name ORDER BY hits DESC nulls LAST) rank,
dense_rank() over(PARTITION BY customer_name ORDER BY hits DESC nulls LAST) denserank,
row_number() over(PARTITION BY customer_name ORDER BY hits DESC) rownumber
FROM
(
SELECT
customer_name,
product_name,
SUM(hits) hits
FROM t
GROUP BY customer_name, product_name
);

CUSTOMER_NAME   PRODUCT_NAME          HITS       RANK  DENSERANK  ROWNUMBER
--------------- --------------- ---------- ---------- ---------- ----------
bill            beer                     5          1          1          1
bill            jeans                    1          2          2          2
bill            egg                      1          2          2          3
larry           cheese                   6          1          1          1
larry           beer                     6          1          1          2
larry           olives                   2          3          2          3
larry           car                      1          4          3          4

7 rows selected.

Observe how dense rank will not skip the ranking sequence when products with the same amount of hits are ranked with the same rank. In this case Larry's cheese and beer are ranked in rank 1 in the DENSERANK column and Larry's olives immediately after that are ranked with 2. Whereas the situation is quite different for this case in the RANK column where Larry's olives are ranked with 3.