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.
Output details
- Return columns named
agent_name, total_assigned, total_recovered, and recovery_rate, in that order.
- Round
recovery_rate to exactly 2 decimal places.
- Treat agents with total assigned amount greater than
0 as eligible; represent no successful collections as 0.
- Use July date bounds inclusive of
2026-07-01 and exclusive of 2026-08-01.
