PhonePe: Collect-Request Senders Declined More Often Than Not
Collect requests are a known route for scams: someone "requests" money hoping the payer approves without reading.
- Difficulty: Medium
- Topics: Aggregation, Conditional Logic
- Dialect: MySQL
- Problem: #887
Problem statement
Collect requests are a known route for scams: someone "requests" money hoping the payer approves without reading. Trust & Safety flags senders whose requests are mostly rejected by the people they target.
Flag a requester who has sent at least 4 requests, of which more than half were declined, with the declines coming from at least 2 different payers. Return requester_id, requests_sent (all their requests, whatever the status) and declined. Order the rows by declined from most to least, then by requester_id.
A practice scenario set at PhonePe: the tables and data are invented, and this is not a question from, or endorsed by, PhonePe.
Tables
Table: CollectRequest
| Column | Type |
|---|---|
| request_id | int |
| requester_id | int |
| payer_id | int |
| amount | int |
| status | enum(approved, declined, expired, pending) |
| created_at | datetime |
Primary key: request_id.
One row per collect request sent by requester_id to payer_id.
Examples
Example 1
CollectRequest
| request_id | requester_id | payer_id | amount | status | created_at |
|---|---|---|---|---|---|
| 1 | 51 | 201 | 4999 | declined | 2025-07-01 10:00:00 |
| 2 | 51 | 202 | 4999 | declined | 2025-07-01 10:05:00 |
| 3 | 51 | 203 | 4999 | declined | 2025-07-01 10:09:00 |
| 4 | 51 | 204 | 4999 | approved | 2025-07-01 10:15:00 |
| 5 | 52 | 205 | 300 | declined | 2025-07-02 09:00:00 |
| 6 | 52 | 205 | 300 | declined | 2025-07-02 09:30:00 |
| 7 | 52 | 205 | 300 | declined | 2025-07-02 10:00:00 |
| 8 | 52 | 206 | 300 | approved | 2025-07-02 11:00:00 |
| 9 | 53 | 201 | 150 | declined | 2025-07-03 12:00:00 |
| 10 | 53 | 202 | 150 | declined | 2025-07-03 12:10:00 |
| 11 | 53 | 203 | 150 | expired | 2025-07-03 12:20:00 |
| 12 | 53 | 204 | 150 | approved | 2025-07-03 12:30:00 |
Output
| requester_id | requests_sent | declined |
|---|---|---|
| 51 | 4 | 3 |
How to solve PhonePe: Collect-Request Senders Declined More Often Than Not
Every condition is a property of a sender's whole history, so the query groups by requester_id and puts all three tests in HAVING. COUNT(*) is the number of requests sent, and SUM(CASE WHEN status = 'declined' THEN 1 ELSE 0 END) the number declined.
"More than half" is strict: three declines out of six is exactly half and is not enough. Writing it as 2 * declined > sent keeps the test in whole numbers. The spread condition needs the number of different payers who declined; COUNT(DISTINCT CASE WHEN status = 'declined' THEN payer_id END) works because the CASE yields NULL for other statuses and COUNT ignores NULLs. In the example sender 52 was declined three times by the same person, which looks like a dispute rather than a scam, so they are not flagged; sender 53 has exactly half declined and is not flagged either.
The alternative computes the totals and the distinct decliners in two derived tables and joins them; requests_sent / 2 is a decimal division, so the comparison stays exact. Both are one aggregate pass over the requests.
Reference solution (MySQL)
SELECT requester_id,
COUNT(*) AS requests_sent,
SUM(CASE WHEN status = 'declined' THEN 1 ELSE 0 END) AS declined
FROM CollectRequest
GROUP BY requester_id
HAVING COUNT(*) >= 4
AND 2 * SUM(CASE WHEN status = 'declined' THEN 1 ELSE 0 END) > COUNT(*)
AND COUNT(DISTINCT CASE WHEN status = 'declined' THEN payer_id END) >= 2
ORDER BY declined DESC, requester_idAnother way
SELECT t.requester_id, t.requests_sent, t.declined
FROM (SELECT requester_id, COUNT(*) AS requests_sent, SUM(IF(status = 'declined', 1, 0)) AS declined FROM CollectRequest GROUP BY requester_id) t
JOIN (SELECT requester_id, COUNT(DISTINCT payer_id) AS decliners FROM CollectRequest WHERE status = 'declined' GROUP BY requester_id) d ON d.requester_id = t.requester_id
WHERE t.requests_sent >= 4 AND t.declined > t.requests_sent / 2 AND d.decliners >= 2
ORDER BY t.declined DESC, t.requester_id← PhonePe: Motor Policy Renewal Rate by Expiry Month · PhonePe: New Users Who Tried Three Products in Their First 30 Days →