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.