Razorpay: Payment Links That Lapsed Without Payment

Easy
898.

Razorpay: Payment Links That Lapsed Without Payment

EasyBasicsDates

MySQL

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

ColumnType
link_idint
merchant_idint
amountint
expire_bydate
paid_ondate
cancelledbool

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.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

PaymentLink

link_idmerchant_idamountexpire_bypaid_oncancelled
11114992025-03-15NULL0
21125002025-03-102025-03-080
3129992025-03-31NULL0
4125000NULLNULL0
5137502025-02-28NULL1
613120002025-03-30NULL0
7143002025-04-15NULL0

Output

link_idmerchant_idamount
1111499
61312000
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.

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

ColumnType
link_idint
merchant_idint
amountint
expire_bydate
paid_ondate
cancelledbool

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_idmerchant_idamountexpire_bypaid_oncancelled
11114992025-03-15NULL0
21125002025-03-102025-03-080
3129992025-03-31NULL0
4125000NULLNULL0
5137502025-02-28NULL1
613120002025-03-30NULL0
7143002025-04-15NULL0

Output

link_idmerchant_idamount
1111499
61312000

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 →