SQL: Ranking

To rank based on number of cases created for customers:

SELECT
       ROW_NUMBER() OVER(ORDER BY COUNT(incidentid) DESC) AS 'Ranking',
       Contact,
       COUNT(incidentid) 'Incident Count'
FROM incident
       GROUP BY Contact
       ORDER BY COUNT(incidentid) DESC

Results:

Ranking Contact              Incident Count
------- -------------------- --------------
1       DEEPAK                110
2       GANESH LAL SHRESTHA   78
3       MIZANUR RAHMAN        36
4       WELLER                34
5       MICHELLE TONG         19

To rank based on number of cases of particular Case Type created for customers:

SELECT
        Contact,
        ROW_NUMBER() OVER(PARTITION BY Contact ORDER BY COUNT(incidentid) DESC) AS 'Ranking',
        CaseType,
        COUNT(incidentid) 'Incident Count'
FROM #incident
        GROUP BY Contact, CaseType
        ORDER BY Contact, COUNT(incidentid) DESC

Results:

Contact                Rank   Case Type      Case Count
---------------------  ------ -------------- ----------
DEEPAK                 1      APPEAL         21
DEEPAK                 2      FEEDBACK       20
DEEPAK                 3      COMPLIMENT     16
DEEPAK                 4      MP APPEAL      15
DEEPAK                 5      SUGGESTION     14
DEEPAK                 6      COMPLAINT      13
DEEPAK                 7      ENQUIRY        11
GANESH LAL SHRESTHA    1      ENQUIRY        16
GANESH LAL SHRESTHA    2      FEEDBACK       14
GANESH LAL SHRESTHA    3      APPEAL         13
GANESH LAL SHRESTHA    4      COMPLIMENT     10
GANESH LAL SHRESTHA    5      SUGGESTION     10
GANESH LAL SHRESTHA    6      COMPLAINT      9
GANESH LAL SHRESTHA    7      MP APPEAL      6
MICHELLE TONG          1      COMPLIMENT     6
MICHELLE TONG          2      COMPLAINT      4
MICHELLE TONG          3      ENQUIRY        3
MICHELLE TONG          4      SUGGESTION     3
MICHELLE TONG          5      APPEAL         2
MICHELLE TONG          6      MP APPEAL      1
MIZANUR RAHMAN         1      ENQUIRY        7
MIZANUR RAHMAN         2      COMPLIMENT     7
MIZANUR RAHMAN         3      FEEDBACK       6
MIZANUR RAHMAN         4      COMPLAINT      5
MIZANUR RAHMAN         5      MP APPEAL      4
MIZANUR RAHMAN         6      SUGGESTION     4
MIZANUR RAHMAN         7      APPEAL         3
WELLER                 1      SUGGESTION     8
WELLER                 2      FEEDBACK       7
WELLER                 3      MP APPEAL      5
WELLER                 4      ENQUIRY        5
WELLER                 5      COMPLIMENT     4
WELLER                 6      APPEAL         3
WELLER                 7      COMPLAINT      2