Razorpay: Fund Accounts With a Malformed UPI ID

Easy
904.

Razorpay: Fund Accounts With a Malformed UPI ID

EasyStrings

MySQL

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

ColumnTypeValues
fund_account_idint
contact_namevarchar
account_typeenumvpa, bank_account
vpavarchar

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.

Example

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

FundAccount

fund_account_idcontact_nameaccount_typevpa
1Aaravvpaaarav.mehta@okhdfcbank
2Diyavpadiya.rao
3Kabirvpa@okaxis
4Meerabank_accountNULL
5Rohanvparohan@@ybl
6Snehavpasneha 21@ybl
7Vikramvpa9876543210@paytm
8ZaravpaNULL

Output

fund_account_idcontact_namevpa
2Diyadiya.rao
3Kabir@okaxis
5Rohanrohan@@ybl
6Snehasneha 21@ybl
8ZaraNULL
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: 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

ColumnType
fund_account_idint
contact_namevarchar
account_typeenum(vpa, bank_account)
vpavarchar

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_idcontact_nameaccount_typevpa
1Aaravvpaaarav.mehta@okhdfcbank
2Diyavpadiya.rao
3Kabirvpa@okaxis
4Meerabank_accountNULL
5Rohanvparohan@@ybl
6Snehavpasneha 21@ybl
7Vikramvpa9876543210@paytm
8ZaravpaNULL

Output

fund_account_idcontact_namevpa
2Diyadiya.rao
3Kabir@okaxis
5Rohanrohan@@ybl
6Snehasneha 21@ybl
8ZaraNULL

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 →