Manual-review reapplication repeat offendersRisk & Fraud

Task prompt

Support wants the repeat manual-review reapplication offenders blocked. The CX ticket about Manual Review Accounts (in Jira) has the support context - users who keep reapplying, dodge documentation, and stay stuck in manual review. Read that ticket, then investigate the warehouse yourself and build the offender cohort as of 2023-07-01. Count at the PERSON level: one person can hold several separate account applications, so group to the person, not to individual applications or accounts. Boundary semantics, so the numbers are unambiguous: count ALL of a person's distinct account applications regardless of date (an application created on or after 2023-07-01 still counts); and treat a documentation request as outstanding only if it was never completed AND it was created more than 30 days before the as-of date (i.e. before 2023-06-01) - requests created on or after 2023-06-01 do not count. A person is a repeat offender only if ALL THREE hold: (a) they have at least 3 distinct account applications; (b) they have at least one outstanding (never-completed, stale-as-defined-above) documentation request; and (c) they are predominantly stuck in the manual-review/rejection loop rather than getting approved. Decide (c) yourself from each person's mix of application outcomes: a repeat offender is someone whose applications mostly end in manual_review or rejected, NOT someone who mostly gets approved (people who mostly get approved are not offenders, even when they still have open doc requests). Figure out the table relationships and the person->application and person->documentation joins from the schema. Report these severity metrics over the qualifying cohort only: repeat_offender_count (the cohort size); total_outstanding_doc_requests (the total number of outstanding documentation requests across the cohort); max_applications_per_person; cohort_manual_review_apps (the total number of manual_review applications across the cohort); and cohort_four_app_users (how many cohort members have exactly 4 applications). When you've got it: open a Slack channel called "mr-reapplication-audit", post a summary there with the cohort size, severity metrics, and your hard-block recommendation and pin it; file an Engineering ticket in project ENG (create the Jira issue with project key ENG) titled "Manual-review reapplication repeat offenders" capturing the repeat-offender cohort and your recommended hard-blocks; and reply to me with repeat_offender_count, total_outstanding_doc_requests, max_applications_per_person, cohort_manual_review_apps, and cohort_four_app_users.