Razorpay: Fund Accounts With a Malformed UPI ID
Before a RazorpayX bulk payout goes out, the payouts team screens the contacts' UPI fund accounts: a payout to a malformed UPI ID (VPA) fails at the bank…
- Difficulty: Easy
- Topics: Strings
- Dialect: MySQL
- Problem: #904
Problem statement
Before a RazorpayX bulk payout goes out, the payouts team screens the contacts' UPI fund accounts: a payout to a malformed UPI ID (VPA) fails at the bank and has to be redone.
A VPA is well formed when it contains exactly one @, has at least one character before and after it, and contains no spaces. Among fund accounts with account_type = 'vpa', return those whose vpa is NULL or not well formed: fund_account_id, contact_name and vpa. Bank-account fund accounts are never reported. 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: FundAccount
| Column | Type |
|---|---|
| fund_account_id | int |
| contact_name | varchar |
| account_type | enum(vpa, bank_account) |
| vpa | varchar |
Primary key: fund_account_id.
Where a contact is paid. vpa is the UPI ID for vpa accounts and NULL for bank accounts (though a broken import can leave it NULL on a vpa account too). All IDs are lower case.
Examples
Example 1
FundAccount
| fund_account_id | contact_name | account_type | vpa |
|---|---|---|---|
| 1 | Aarav | vpa | aarav.mehta@okhdfcbank |
| 2 | Diya | vpa | diya.rao |
| 3 | Kabir | vpa | @okaxis |
| 4 | Meera | bank_account | NULL |
| 5 | Rohan | vpa | rohan@@ybl |
| 6 | Sneha | vpa | sneha 21@ybl |
| 7 | Vikram | vpa | 9876543210@paytm |
| 8 | Zara | vpa | NULL |
Output
| fund_account_id | contact_name | vpa |
|---|---|---|
| 2 | Diya | diya.rao |
| 3 | Kabir | @okaxis |
| 5 | Rohan | rohan@@ybl |
| 6 | Sneha | sneha 21@ybl |
| 8 | Zara | NULL |
How to solve Razorpay: Fund Accounts With a Malformed UPI ID
The rule has three parts — exactly one @, something on both sides of it, no spaces — and a single anchored regular expression captures all of them: ^[^@ ]+@[^@ ]+$ means one or more characters that are neither @ nor a space, then the @, then one or more such characters, and nothing else. A VPA that fails to match is malformed.
NULL needs its own branch. NULL NOT REGEXP … is NULL, not true, so without vpa IS NULL OR … a vpa account with no ID would slip through the screen — the opposite of what the payouts team wants. The empty string fails the pattern, so it is reported as well.
The alternative avoids regex: LENGTH(vpa) - LENGTH(REPLACE(vpa, '@', '')) counts the @ signs, LEFT/RIGHT check the first and last characters, and LOCATE(' ', vpa) = 0 rules out spaces; COALESCE(…, 0) turns the NULL case into "not well formed". Both are one scan of the table.
Reference solution (MySQL)
SELECT fund_account_id, contact_name, vpa
FROM FundAccount
WHERE account_type = 'vpa'
AND (vpa IS NULL OR vpa NOT REGEXP '^[^@ ]+@[^@ ]+$')Another way
SELECT fund_account_id, contact_name, vpa
FROM FundAccount
WHERE account_type = 'vpa'
AND NOT COALESCE(LENGTH(vpa) - LENGTH(REPLACE(vpa, '@', '')) = 1
AND LEFT(vpa, 1) <> '@' AND RIGHT(vpa, 1) <> '@'
AND LOCATE(' ', vpa) = 0, 0)← Razorpay: Normal-Speed Refunds That Took Over a Week · Razorpay: Magic Checkout Conversion Rate by Merchant →