PhonePe: Collect Requests Still Pending After Their Expiry

Easy
878.

PhonePe: Collect Requests Still Pending After Their Expiry

EasyDates

MySQL

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

ColumnTypeValues
request_idint
requester_vpavarchar
payer_vpavarchar
amountint
created_atdatetime
expires_atdatetime
statusenumpending, approved, declined, expired

Primary key: request_id.

One row per collect request; times are whole minutes.

Example

The rows must come back in the order shown.

CollectRequest

request_idrequester_vpapayer_vpaamountcreated_atexpires_atstatus
1shop.fresh@yblaarav@ibl4502025-03-09 10:00:002025-03-09 10:30:00pending
2kabir22@ybldiya.m4@axl12002025-03-10 09:00:002025-03-10 12:00:00pending
3kabir22@yblmeera@ybl3002025-03-10 11:00:002025-03-10 11:15:00pending
4zara@iblrohan9@ybl8002025-03-08 08:00:002025-03-08 09:00:00approved
5shop.fresh@yblishaan@axl6502025-03-10 11:30:002025-03-10 12:30:00pending
6zara@iblneha@ybl902025-03-10 10:45:002025-03-10 11:15:00pending

Output

request_idpayer_vpaamountminutes_overdue
1aarav@ibl4501530
3meera@ybl30045
6neha@ybl9045
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 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

ColumnType
request_idint
requester_vpavarchar
payer_vpavarchar
amountint
created_atdatetime
expires_atdatetime
statusenum(pending, approved, declined, expired)

Primary key: request_id.

One row per collect request; times are whole minutes.

Examples

Example 1

CollectRequest

request_idrequester_vpapayer_vpaamountcreated_atexpires_atstatus
1shop.fresh@yblaarav@ibl4502025-03-09 10:00:002025-03-09 10:30:00pending
2kabir22@ybldiya.m4@axl12002025-03-10 09:00:002025-03-10 12:00:00pending
3kabir22@yblmeera@ybl3002025-03-10 11:00:002025-03-10 11:15:00pending
4zara@iblrohan9@ybl8002025-03-08 08:00:002025-03-08 09:00:00approved
5shop.fresh@yblishaan@axl6502025-03-10 11:30:002025-03-10 12:30:00pending
6zara@iblneha@ybl902025-03-10 10:45:002025-03-10 11:15:00pending

Output

request_idpayer_vpaamountminutes_overdue
1aarav@ibl4501530
3meera@ybl30045
6neha@ybl9045

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_id

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