Paytm: Mobile Numbers That Switched Operator Between Recharges

Medium
863.

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. 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

ColumnTypeValues
recharge_idint
mobile_nobigint
operatorenumJio, Airtel, Vi, BSNL
recharged_atdatetime
amountint

Primary key: recharge_id.

One row per successful prepaid recharge; operator is the network the recharge was sent to.

Example

The rows must come back in the order shown.

PrepaidRecharge

recharge_idmobile_nooperatorrecharged_atamount
19810000001Airtel2025-01-03 10:00:00299
29810000001Airtel2025-02-02 10:00:00299
39810000001Jio2025-03-04 09:00:00349
49810000002Vi2025-01-10 08:00:00199
59810000002Jio2025-02-11 08:00:00239
69810000002Vi2025-03-12 08:00:00199
79810000003Jio2025-01-15 20:00:00719
89810000004BSNL2025-01-20 07:30:00107
99810000004Airtel2025-02-25 07:30:00299
109810000005Jio2025-01-05 12:00:00239
1 more row

Output

mobile_nofirst_operatorlatest_operator
9810000001AirtelJio
9810000004BSNLAirtel
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: 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

ColumnType
recharge_idint
mobile_nobigint
operatorenum(Jio, Airtel, Vi, BSNL)
recharged_atdatetime
amountint

Primary key: recharge_id.

One row per successful prepaid recharge; operator is the network the recharge was sent to.

Examples

Example 1

PrepaidRecharge

recharge_idmobile_nooperatorrecharged_atamount
19810000001Airtel2025-01-03 10:00:00299
29810000001Airtel2025-02-02 10:00:00299
39810000001Jio2025-03-04 09:00:00349
49810000002Vi2025-01-10 08:00:00199
59810000002Jio2025-02-11 08:00:00239
69810000002Vi2025-03-12 08:00:00199
79810000003Jio2025-01-15 20:00:00719
89810000004BSNL2025-01-20 07:30:00107
99810000004Airtel2025-02-25 07:30:00299
109810000005Jio2025-01-05 12:00:00239
119810000005Jio2025-02-05 12:00:00239

Output

mobile_nofirst_operatorlatest_operator
9810000001AirtelJio
9810000004BSNLAirtel

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_no

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

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