PhonePe: Collect-Request Senders Declined More Often Than Not

Medium
887.

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. 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

ColumnTypeValues
request_idint
requester_idint
payer_idint
amountint
statusenumapproved, declined, expired, pending
created_atdatetime

Primary key: request_id.

One row per collect request sent by requester_id to payer_id.

Example

The rows must come back in the order shown.

CollectRequest

request_idrequester_idpayer_idamountstatuscreated_at
1512014999declined2025-07-01 10:00:00
2512024999declined2025-07-01 10:05:00
3512034999declined2025-07-01 10:09:00
4512044999approved2025-07-01 10:15:00
552205300declined2025-07-02 09:00:00
652205300declined2025-07-02 09:30:00
752205300declined2025-07-02 10:00:00
852206300approved2025-07-02 11:00:00
953201150declined2025-07-03 12:00:00
1053202150declined2025-07-03 12:10:00
2 more rows

Output

requester_idrequests_sentdeclined
5143
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
request_idint
requester_idint
payer_idint
amountint
statusenum(approved, declined, expired, pending)
created_atdatetime

Primary key: request_id.

One row per collect request sent by requester_id to payer_id.

Examples

Example 1

CollectRequest

request_idrequester_idpayer_idamountstatuscreated_at
1512014999declined2025-07-01 10:00:00
2512024999declined2025-07-01 10:05:00
3512034999declined2025-07-01 10:09:00
4512044999approved2025-07-01 10:15:00
552205300declined2025-07-02 09:00:00
652205300declined2025-07-02 09:30:00
752205300declined2025-07-02 10:00:00
852206300approved2025-07-02 11:00:00
953201150declined2025-07-03 12:00:00
1053202150declined2025-07-03 12:10:00
1153203150expired2025-07-03 12:20:00
1253204150approved2025-07-03 12:30:00

Output

requester_idrequests_sentdeclined
5143

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_id

Another 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 →