Paytm: Merchant QR Codes Still on the Old @paytm Handle

Easy
850.

Paytm: Merchant QR Codes Still on the Old @paytm Handle

EasyStrings

MySQL

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

ColumnType
merchant_idint
business_namevarchar
upi_idvarchar

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.

Example

The rows must come back in the order shown.

Merchant

merchant_idbusiness_nameupi_id
1Sharma General Storesharmagen@paytm
2Balaji Medicalspaytm.balaji@pthdfc
3Annapurna Sweetspaytmqr1x9k2@ptys
4Gupta KiranaNULL
5Krishna Dairykrishnadairy@paytm
6Raj Electronicsrajelec.paytm@ptsbi
7Fresh Bakery9123456780@paytm

Output

merchant_idbusiness_nameupi_username
1Sharma General Storesharmagen
5Krishna Dairykrishnadairy
7Fresh Bakery9123456780
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.

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

ColumnType
merchant_idint
business_namevarchar
upi_idvarchar

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_idbusiness_nameupi_id
1Sharma General Storesharmagen@paytm
2Balaji Medicalspaytm.balaji@pthdfc
3Annapurna Sweetspaytmqr1x9k2@ptys
4Gupta KiranaNULL
5Krishna Dairykrishnadairy@paytm
6Raj Electronicsrajelec.paytm@ptsbi
7Fresh Bakery9123456780@paytm

Output

merchant_idbusiness_nameupi_username
1Sharma General Storesharmagen
5Krishna Dairykrishnadairy
7Fresh Bakery9123456780

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_id

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

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