Paytm: Mobile Numbers That Switched Operator Between Recharges
When a customer ports a number to another network, their next recharge on the app goes to a different operator.
- Difficulty: Medium
- Topics: Window Functions, Subqueries
- Dialect: MySQL
- Problem: #863
Problem statement
When a customer ports a number to another network, their next recharge on the app goes to a different operator. The recharges team tracks these switches to keep operator detection accurate.
Return every mobile number whose latest recharge was with a different operator from its first recharge, with the columns mobile_no, first_operator and latest_operator. A number that left and came back to the same operator is not a switch, and a number with one recharge cannot have switched. No number has two recharges at the same moment. Return the rows ordered by mobile_no.
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: PrepaidRecharge
| Column | Type |
|---|---|
| recharge_id | int |
| mobile_no | bigint |
| operator | enum(Jio, Airtel, Vi, BSNL) |
| recharged_at | datetime |
| amount | int |
Primary key: recharge_id.
One row per successful prepaid recharge; operator is the network the recharge was sent to.
Examples
Example 1
PrepaidRecharge
| recharge_id | mobile_no | operator | recharged_at | amount |
|---|---|---|---|---|
| 1 | 9810000001 | Airtel | 2025-01-03 10:00:00 | 299 |
| 2 | 9810000001 | Airtel | 2025-02-02 10:00:00 | 299 |
| 3 | 9810000001 | Jio | 2025-03-04 09:00:00 | 349 |
| 4 | 9810000002 | Vi | 2025-01-10 08:00:00 | 199 |
| 5 | 9810000002 | Jio | 2025-02-11 08:00:00 | 239 |
| 6 | 9810000002 | Vi | 2025-03-12 08:00:00 | 199 |
| 7 | 9810000003 | Jio | 2025-01-15 20:00:00 | 719 |
| 8 | 9810000004 | BSNL | 2025-01-20 07:30:00 | 107 |
| 9 | 9810000004 | Airtel | 2025-02-25 07:30:00 | 299 |
| 10 | 9810000005 | Jio | 2025-01-05 12:00:00 | 239 |
| 11 | 9810000005 | Jio | 2025-02-05 12:00:00 | 239 |
Output
| mobile_no | first_operator | latest_operator |
|---|---|---|
| 9810000001 | Airtel | Jio |
| 9810000004 | BSNL | Airtel |
How to solve Paytm: Mobile Numbers That Switched Operator Between Recharges
The question only looks at two rows per number: the first recharge and the latest. Numbering the rows twice does it cleanly — ROW_NUMBER() ordered by recharged_at ascending marks the first with 1, and ordered descending marks the latest with 1. Joining the "first" row to the "latest" row of the same number and keeping pairs whose operators differ gives the switches.
Only the endpoints count: number 2 went Vi → Jio → Vi, so its first and latest operators are both Vi and it is not reported. A number with one recharge is its own first and latest row, so the operators are equal and it drops out with no special case.
The alternatives fetch the endpoints differently: correlated subqueries with ORDER BY … LIMIT 1 per number, or FIRST_VALUE/LAST_VALUE over a frame covering the whole partition (the explicit ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING matters — the default frame ends at the current row, which would make LAST_VALUE return the current operator). All are a sort per number.
Reference solution (MySQL)
WITH ordered_r AS (
SELECT mobile_no, operator,
ROW_NUMBER() OVER (PARTITION BY mobile_no ORDER BY recharged_at) AS from_start,
ROW_NUMBER() OVER (PARTITION BY mobile_no ORDER BY recharged_at DESC) AS from_end
FROM PrepaidRecharge
)
SELECT f.mobile_no, f.operator AS first_operator, l.operator AS latest_operator
FROM ordered_r f
JOIN ordered_r l ON l.mobile_no = f.mobile_no AND l.from_end = 1
WHERE f.from_start = 1 AND f.operator <> l.operator
ORDER BY f.mobile_noAnother way
SELECT mobile_no, first_operator, latest_operator FROM (
SELECT p.mobile_no,
(SELECT a.operator FROM PrepaidRecharge a WHERE a.mobile_no = p.mobile_no ORDER BY a.recharged_at LIMIT 1) AS first_operator,
(SELECT b.operator FROM PrepaidRecharge b WHERE b.mobile_no = p.mobile_no ORDER BY b.recharged_at DESC LIMIT 1) AS latest_operator
FROM (SELECT DISTINCT mobile_no FROM PrepaidRecharge) p
) x WHERE first_operator <> latest_operator ORDER BY mobile_noAnother way
SELECT DISTINCT mobile_no, first_operator, latest_operator FROM (
SELECT mobile_no,
FIRST_VALUE(operator) OVER (PARTITION BY mobile_no ORDER BY recharged_at ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_operator,
LAST_VALUE(operator) OVER (PARTITION BY mobile_no ORDER BY recharged_at ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS latest_operator
FROM PrepaidRecharge
) x WHERE first_operator <> latest_operator ORDER BY mobile_no← Paytm: Flight Cancellation Refunds Under the Free Window · Paytm: Cashback Paid Above the Per-User Campaign Cap →