Rank by Agent and Month

Question

I have a table with similar data to the following:

Agent | TCV | Parent AccountID | AccountID | Month
------+-----+------------------+-----------+--------
John  | 100 | ABC12345         | Sept13445 | 2
John  | 200 | ABC12345         | Sept345   | 2 
John  | 150 | CDE12345         | Sept546   | 2
John  | 200 | FTE1456          | Oct3467   | 2
John  | 100 | ABC12345         | Sept13445 | 3
John  | 200 | ABC12345         | Sept345   | 3 
John  | 150 | CDE12345         | Sept546   | 3
John  | 200 | FTE1456          | Oct3467   | 3

What I need is a way to show a grouped ranking by agent, then parent accounrDID each month for each agent. The idea being that in the export, there'd be a column for agent, TCV, parentaccountDID, and month.

So if all agents had 10 parentaccountDIDs (but potentially multiple accountDIDs under each), it would rank them by the grouped TCV of the parentAccountDID. So there'd be 10 lines of data for John's 10 parentaccountDIDs based off of grouped TCV in month 2, then 10 lines of data for the 10 parentaccountDIDs based off of grouped TCV in month 3, etc. etc.

Answer 1

SELECT agent, tcv, parent_accnt_id, accnt_id, curr_month
     , ROW_NUMBER() OVER (PARTITION BY curr_month, parent_accnt_id ORDER BY curr_month) rnk 
 FROM your_table
/

With your data this is what you get:

AGENT    TCV    PARENT_ACCNT_ID    ACCNT_ID    CURR_MONTH    RNK
------------------------------------------------------------------
John     100    ABC12345           Sept13445     2            1
John     200    ABC12345           Sept345       2            2
John     150    CDE12345           Sept546       2            1
John     200    FTE1456            Oct3467       2            1
John     100    ABC12345           Sept13445     3            1
John     200    ABC12345           Sept345       3            2
John     150    CDE12345           Sept546       3            1
John     200    FTE1456            Oct3467       3            1