Razorpay: Payment Links That Lapsed Without Payment
A merchant success manager at Razorpay wants to nudge merchants whose payment links quietly expired before the customer paid, so they can resend them.
- Difficulty: Easy
- Topics: Basics, Dates
- Dialect: MySQL
- Problem: #898
Problem statement
A merchant success manager at Razorpay wants to nudge merchants whose payment links quietly expired before the customer paid, so they can resend them.
As of the audit date 2025-03-31, return every payment link that is not cancelled, has no payment (paid_on is NULL), and whose expire_by date is strictly before 2025-03-31. A link with a NULL expire_by never expires, and a link expiring on the audit date itself has not lapsed yet. Return link_id, merchant_id and amount, in any order.
A practice scenario set at Razorpay: the tables and data are invented, and this is not a question from, or endorsed by, Razorpay.
Tables
Table: PaymentLink
| Column | Type |
|---|---|
| link_id | int |
| merchant_id | int |
| amount | int |
| expire_by | date |
| paid_on | date |
| cancelled | bool |
Primary key: link_id.
One row per payment link a merchant created. amount is in rupees; expire_by is NULL for a link without expiry; paid_on is NULL until the customer pays; cancelled is 1 when the merchant withdrew the link.
Examples
Example 1
PaymentLink
| link_id | merchant_id | amount | expire_by | paid_on | cancelled |
|---|---|---|---|---|---|
| 1 | 11 | 1499 | 2025-03-15 | NULL | 0 |
| 2 | 11 | 2500 | 2025-03-10 | 2025-03-08 | 0 |
| 3 | 12 | 999 | 2025-03-31 | NULL | 0 |
| 4 | 12 | 5000 | NULL | NULL | 0 |
| 5 | 13 | 750 | 2025-02-28 | NULL | 1 |
| 6 | 13 | 12000 | 2025-03-30 | NULL | 0 |
| 7 | 14 | 300 | 2025-04-15 | NULL | 0 |
Output
| link_id | merchant_id | amount |
|---|---|---|
| 1 | 11 | 1499 |
| 6 | 13 | 12000 |
How to solve Razorpay: Payment Links That Lapsed Without Payment
Each link is judged on its own row, so this is a filter with three conditions joined by AND: cancelled = 0 drops the links the merchant withdrew, paid_on IS NULL keeps only links nobody paid, and expire_by < '2025-03-31' keeps links whose validity ended before the audit day.
Two edge cases are handled by the comparison itself. A link with no expiry has expire_by NULL; NULL < '2025-03-31' is unknown, and WHERE keeps only true rows, so such links are never reported — they cannot lapse. A link expiring on 2025-03-31 is still valid that day, and the strict < leaves it out.
The alternative spells the date test as DATEDIFF('2025-03-31', expire_by) > 0 with an explicit NOT NULL check, and the payment test with COALESCE. Either way it is one scan of the table; an index on (paid_on, expire_by) would narrow it to unpaid links.
Reference solution (MySQL)
SELECT link_id, merchant_id, amount
FROM PaymentLink
WHERE cancelled = 0
AND paid_on IS NULL
AND expire_by < '2025-03-31'Another way
SELECT link_id, merchant_id, amount FROM PaymentLink WHERE NOT cancelled = 1 AND COALESCE(paid_on, 'unpaid') = 'unpaid' AND expire_by IS NOT NULL AND DATEDIFF('2025-03-31', expire_by) > 0← PhonePe: Three-Way Circular Transfers Within 24 Hours · Razorpay: Captured Volume by Payment Method per Merchant →