PhonePe: Collect Requests Still Pending After Their Expiry
A collect request asks a payer to approve a payment before expires_at. A sweeper job should flip unanswered requests to expired; on-call suspects it stalled.
- Difficulty: Easy
- Topics: Dates
- Dialect: MySQL
- Problem: #878
Problem statement
A collect request asks a payer to approve a payment before expires_at. A sweeper job should flip unanswered requests to expired; on-call suspects it stalled. At the audit time 2025-03-10 12:00:00 they want every request still pending whose expiry has already passed.
Return request_id, payer_vpa, amount and minutes_overdue, the whole minutes from expires_at to the audit time. A request expiring exactly at the audit time is not overdue yet. Order the rows by minutes_overdue from most to least, then by request_id ascending.
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_vpa | varchar |
| payer_vpa | varchar |
| amount | int |
| created_at | datetime |
| expires_at | datetime |
| status | enum(pending, approved, declined, expired) |
Primary key: request_id.
One row per collect request; times are whole minutes.
Examples
Example 1
CollectRequest
| request_id | requester_vpa | payer_vpa | amount | created_at | expires_at | status |
|---|---|---|---|---|---|---|
| 1 | shop.fresh@ybl | aarav@ibl | 450 | 2025-03-09 10:00:00 | 2025-03-09 10:30:00 | pending |
| 2 | kabir22@ybl | diya.m4@axl | 1200 | 2025-03-10 09:00:00 | 2025-03-10 12:00:00 | pending |
| 3 | kabir22@ybl | meera@ybl | 300 | 2025-03-10 11:00:00 | 2025-03-10 11:15:00 | pending |
| 4 | zara@ibl | rohan9@ybl | 800 | 2025-03-08 08:00:00 | 2025-03-08 09:00:00 | approved |
| 5 | shop.fresh@ybl | ishaan@axl | 650 | 2025-03-10 11:30:00 | 2025-03-10 12:30:00 | pending |
| 6 | zara@ibl | neha@ybl | 90 | 2025-03-10 10:45:00 | 2025-03-10 11:15:00 | pending |
Output
| request_id | payer_vpa | amount | minutes_overdue |
|---|---|---|---|
| 1 | aarav@ibl | 450 | 1530 |
| 3 | meera@ybl | 300 | 45 |
| 6 | neha@ybl | 90 | 45 |
How to solve PhonePe: Collect Requests Still Pending After Their Expiry
A request is stuck when two facts hold: nobody answered it (status = 'pending') and its window has closed (expires_at < '2025-03-10 12:00:00'). Datetimes in the fixed YYYY-MM-DD HH:MM:SS form compare correctly, so the second test is a plain comparison. It is strict, because at exactly 12:00:00 the request has not expired yet.
How late it is comes from TIMESTAMPDIFF(MINUTE, expires_at, audit_time): the first argument is the earlier time, so the result is positive for every row the filter keeps. Approved, declined and already-expired requests are excluded by the status test even when their expiry is in the past.
The order is fixed: most overdue first, and request_id to settle two requests that expired in the same minute. The alternative measures in seconds and divides with DIV 60, which equals the minute count because every time is a whole minute. One scan; an index on (status, expires_at) would serve both filters.
Reference solution (MySQL)
SELECT request_id, payer_vpa, amount,
TIMESTAMPDIFF(MINUTE, expires_at, '2025-03-10 12:00:00') AS minutes_overdue
FROM CollectRequest
WHERE status = 'pending'
AND expires_at < '2025-03-10 12:00:00'
ORDER BY minutes_overdue DESC, request_idAnother way
SELECT request_id, payer_vpa, amount, TIMESTAMPDIFF(SECOND, expires_at, '2025-03-10 12:00:00') DIV 60 AS minutes_overdue FROM CollectRequest WHERE status = 'pending' AND TIMESTAMPDIFF(SECOND, expires_at, '2025-03-10 12:00:00') > 0 ORDER BY 4 DESC, 1← PhonePe: Masked UPI IDs on Payment Receipts · PhonePe: Delivered Pincode Orders per Partner Store, Zeros Included →