Does this mean that the software package manages the permissions of each user in the system?  Does the software package have any means to audit the work being done by...

So what all have you tried?  Do you have the actually execution plans of the various options? What indexing exists on these large tables?

Simple:

WITH BaseData as (
SELECT
    AgentID
    , WC
    , RecruitmentDate
    , [Agent preferred gender]
    , [Agent Gender at Birth]
    , [rn] = ROW_NUMBER() OVER (PARTITION BY...