Paytm: Merchant QR Codes Still on the Old @paytm Handle
Merchant QR codes are being moved from the old @paytm UPI handle to partner-bank handles such as @ptys, @pthdfc, @ptsbi and @ptaxis.
- Difficulty: Easy
- Topics: Strings
- Dialect: MySQL
- Problem: #850
Problem statement
Merchant QR codes are being moved from the old @paytm UPI handle to partner-bank handles such as @ptys, @pthdfc, @ptsbi and @ptaxis. The migration team needs the list of merchants still on the old one.
A UPI ID has the form username@handle with exactly one @. Return every merchant whose handle is exactly paytm, with the columns merchant_id, business_name and upi_username (the part before the @). A NULL upi_id never qualifies, and the word paytm inside a username does not make it the old handle. Return the rows ordered by merchant_id.
A practice scenario set at Paytm: the tables and data are invented, and this is not a question from, or endorsed by, Paytm.
Tables
Table: Merchant
| Column | Type |
|---|---|
| merchant_id | int |
| business_name | varchar |
| upi_id | varchar |
Primary key: merchant_id.
upi_id is the ID printed on the merchant's QR code, all lower case, or NULL while onboarding is incomplete.
Examples
Example 1
Merchant
| merchant_id | business_name | upi_id |
|---|---|---|
| 1 | Sharma General Store | sharmagen@paytm |
| 2 | Balaji Medicals | paytm.balaji@pthdfc |
| 3 | Annapurna Sweets | paytmqr1x9k2@ptys |
| 4 | Gupta Kirana | NULL |
| 5 | Krishna Dairy | krishnadairy@paytm |
| 6 | Raj Electronics | rajelec.paytm@ptsbi |
| 7 | Fresh Bakery | 9123456780@paytm |
Output
| merchant_id | business_name | upi_username |
|---|---|---|
| 1 | Sharma General Store | sharmagen |
| 5 | Krishna Dairy | krishnadairy |
| 7 | Fresh Bakery | 9123456780 |
How to solve Paytm: Merchant QR Codes Still on the Old @paytm Handle
The question is about the handle, the part after the @, so the query has to split the string rather than search it. SUBSTRING_INDEX(upi_id, '@', -1) returns everything after the last @ and SUBSTRING_INDEX(upi_id, '@', 1) everything before the first; with exactly one @ they are the handle and the username. Comparing the handle with = 'paytm' keeps sharmagen@paytm and rejects paytm.balaji@pthdfc, whose username merely contains the word — the trap a LIKE '%paytm%' falls into.
A NULL upi_id makes SUBSTRING_INDEX return NULL, and NULL = 'paytm' is not true, so merchants still onboarding drop out without a special case.
The alternatives anchor the match at the end instead: LIKE '%@paytm' (the @ in the pattern stops a handle like ptpaytm matching) with LOCATE to cut the username, or RIGHT(upi_id, 6) = '@paytm' with a length-based SUBSTRING. All of them read every row once; a leading wildcard cannot use an index, which is fine for a one-off migration list.
Reference solution (MySQL)
SELECT merchant_id, business_name, SUBSTRING_INDEX(upi_id, '@', 1) AS upi_username
FROM Merchant
WHERE SUBSTRING_INDEX(upi_id, '@', -1) = 'paytm'
ORDER BY merchant_idAnother way
SELECT merchant_id, business_name, LEFT(upi_id, LOCATE('@', upi_id) - 1) AS upi_username FROM Merchant WHERE upi_id LIKE '%@paytm' ORDER BY merchant_idAnother way
SELECT merchant_id, business_name, SUBSTRING(upi_id, 1, CHAR_LENGTH(upi_id) - 6) AS upi_username FROM Merchant WHERE RIGHT(upi_id, 6) = '@paytm' ORDER BY merchant_id← Paytm: Soundbox Devices That Never Announced a Payment · Paytm: Wallet Users by KYC Level in Each City →