SQL Substring and Length | Dataford Interview Questions

PhonePe UPI operations analysts need the final four characters of each successful numeric UPI reference for reconciliation. Write a PostgreSQL query that joins transactions to merchants and extracts this suffix using LENGTH and SUBSTR.

Requirements

Return only transactions whose status is SUCCESS.
Extract the last four characters of upi_reference using SUBSTR(upi_reference, LENGTH(upi_reference) - 3, 4).
Preserve successful transactions even when no merchant record or UPI reference exists, and order the output by transaction_id.

Schema

phonepe_transactions

Column	Type	Description
transaction_idPK	INT	Unique transaction identifier
merchant_id	INT	Associated merchant identifier
upi_reference	VARCHAR(30)	Numeric UPI reference string
status	VARCHAR(20)	Transaction processing status
transaction_type	VARCHAR(20)	Payment or refund classification

phonepe_merchants

Column	Type	Description
merchant_idPK	INT	Unique merchant identifier
merchant_name	VARCHAR(100)	Merchant display name
city	VARCHAR(50)	Merchant operating city

Your question is SQL Substring and Length. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem