Paytm Operations needs to identify the most effective collection agents based on recovery performance. Assume the query runs on 2026-08-29, so the last calendar month is July 2026.
Write a PostgreSQL query to return the top 5 agents by collection recovery rate for cases assigned during July 2026.
Requirements
- Calculate recovery rate as successful July collections divided by the total assigned amount for July cases, multiplied by 100.
- Include agents with assigned July cases but no successful collections, while excluding agents with no assigned amount.
- Consider only payments with
payment_status = 'SUCCESS' and a July 2026 payment date.
- Return the agent name, total assigned amount, total recovered amount, and recovery rate, ordered by recovery rate descending, then recovered amount descending, and agent ID ascending for deterministic ties.
