Below is the table:
category weightage As_of_date 123abc 50 1/1/2020 456abc 100 1/2/2020 456abc 100 1/3/2020 678def 200 1/4/2020 678def 200 1/4/2020 123def 50 2/1/2020 123def 50 2/1/2020 123def 50 2/3/2020 123def 50 2/1/2020 123def 50 6/7/2020
where I want to rank the category based on weightage desc, expected results:
category weightage As_of_date dense_rank 123abc 50 1/1/2020 4 456abc 100 1/2/2020 3 456abc 100 1/3/2020 3 678def 200 1/4/2020 1 678def 200 1/4/2020 1 123def 50 2/1/2020 2 123def 50 2/1/2020 2 123def 50 2/3/2020 2 123def 50 2/1/2020 2 123def 50 6/7/2020 2
what was already tried: select desnse_rank() over (partition by category order by weightage desc), but I need to rank it based on sum(weightage) per category.
Advertisement
Answer
With a CTE you can do the calculations one by one. First calculate the dense rank for sum per category (ranked_by_sum), then join back to the original table to get the dense rank value for the individual rows:
WITH test_data (category, weightage, as_of_date) AS
(
SELECT '123abc',50, TO_DATE('1/1/2020','DD/MM/YYYY') FROM DUAL UNION ALL
SELECT '456abc',100,TO_DATE('1/2/2020','DD/MM/YYYY') FROM DUAL UNION ALL
SELECT '456abc',100,TO_DATE('1/3/2020','DD/MM/YYYY') FROM DUAL UNION ALL
SELECT '678def',200,TO_DATE('1/4/2020','DD/MM/YYYY') FROM DUAL UNION ALL
SELECT '678def',200,TO_DATE('1/4/2020','DD/MM/YYYY') FROM DUAL UNION ALL
SELECT '123def',50, TO_DATE('2/1/2020','DD/MM/YYYY') FROM DUAL UNION ALL
SELECT '123def',50, TO_DATE('2/1/2020','DD/MM/YYYY') FROM DUAL UNION ALL
SELECT '123def',50, TO_DATE('2/3/2020','DD/MM/YYYY') FROM DUAL UNION ALL
SELECT '123def',50, TO_DATE('2/1/2020','DD/MM/YYYY') FROM DUAL UNION ALL
SELECT '123def',50, TO_DATE('6/7/2020','DD/MM/YYYY') FROM DUAL
), ranked_by_sum (category,sum_weightage, drnk)
AS
(
SELECT category, SUM(weightage),DENSE_RANK () OVER (
ORDER BY SUM(weightage) DESC )
FROM test_data
GROUP BY category
)
SELECT t.category, t.weightage, t.as_of_date, r.drnk
FROM test_data t
JOIN ranked_by_sum r ON t.category = r.category
ORDER BY r.drnk DESC
CATEGO WEIGHTAGE AS_OF_DATE DRNK
------ ---------- ----------- ----------
123abc 50 01-JAN-2020 4
456abc 100 01-FEB-2020 3
456abc 100 01-MAR-2020 3
123def 50 02-JAN-2020 2
123def 50 06-JUL-2020 2
123def 50 02-JAN-2020 2
123def 50 02-JAN-2020 2
123def 50 02-MAR-2020 2
678def 200 01-APR-2020 1
678def 200 01-APR-2020 1