Your question is Top Agents by Recovery Rate SQL. Start with the requirements and the three tables on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
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.
payment_status = 'SUCCESS' and a July 2026 payment date.| Column | Type | Description |
|---|---|---|
| agent_idPK | INT | Unique collection agent identifier |
| agent_name | VARCHAR(100) | Collection agent's name |
| Column | Type | Description |
|---|---|---|
| case_idPK | INT | Unique collection case identifier |
| agent_id | INT | Agent assigned to the case |
| assigned_date | DATE | Date the case was assigned |
| assigned_amount | NUMERIC(12,2) | Amount assigned for recovery |
| Column | Type | Description |
|---|---|---|
| payment_idPK | INT | Unique payment identifier |
| case_id | INT | Collection case associated with the payment |
| payment_date | DATE | Date the payment was received |
| payment_amount | NUMERIC(12,2) | Amount received in the payment |
| payment_status | VARCHAR(20) | Payment processing status |
| payment_method | VARCHAR(30) | Payment channel used |