Prepaid Service Lapses Between Recharges

Medium
515.

Prepaid Service Lapses Between Recharges

When a prepaid plan expires before the next recharge, the number is without service for a while. A plan bought on recharge_date expires on recharge_date plus validity_days days. Using the operator's simple rule, compare every recharge only with the next recharge of the same number: if the next recharge is strictly after the expiry date, the number lapsed.

Return every lapse with the columns mobile_number, expired_on (the expiry date), recharged_on (the next recharge's date) and lapse_days (days between them). The last recharge of a number has no next one and is never a lapse. Order by mobile_number, then expired_on, then recharged_on.

Tables

Table: Recharge

ColumnType
recharge_idint
mobile_numbervarchar
recharge_datedate
validity_daysint

Primary key: recharge_id.

A number recharges at most once a day.

Example

The rows must come back in the order shown.

Recharge

recharge_idmobile_numberrecharge_datevalidity_days
198450123452024-01-0128
298450123452024-01-2928
398450123452024-03-0456
499001234562024-01-1084
599001234562024-02-1528
699001234562024-03-2028
781234567892024-02-0128
870199887762024-01-0514
970199887762024-01-2028

Output

mobile_numberexpired_onrecharged_onlapse_days
70199887762024-01-192024-01-201
98450123452024-02-262024-03-047
99001234562024-03-142024-03-206
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.

Prepaid Service Lapses Between Recharges — SQL Problem

When a prepaid plan expires before the next recharge, the number is without service for a while.

  • Difficulty: Medium
  • Topics: Window Functions, Dates
  • Dialect: MySQL
  • Problem: #515

Problem statement

When a prepaid plan expires before the next recharge, the number is without service for a while. A plan bought on recharge_date expires on recharge_date plus validity_days days. Using the operator's simple rule, compare every recharge only with the next recharge of the same number: if the next recharge is strictly after the expiry date, the number lapsed.

Return every lapse with the columns mobile_number, expired_on (the expiry date), recharged_on (the next recharge's date) and lapse_days (days between them). The last recharge of a number has no next one and is never a lapse. Order by mobile_number, then expired_on, then recharged_on.

Tables

Table: Recharge

ColumnType
recharge_idint
mobile_numbervarchar
recharge_datedate
validity_daysint

Primary key: recharge_id.

A number recharges at most once a day.

Examples

Example 1

Recharge

recharge_idmobile_numberrecharge_datevalidity_days
198450123452024-01-0128
298450123452024-01-2928
398450123452024-03-0456
499001234562024-01-1084
599001234562024-02-1528
699001234562024-03-2028
781234567892024-02-0128
870199887762024-01-0514
970199887762024-01-2028

Output

mobile_numberexpired_onrecharged_onlapse_days
70199887762024-01-192024-01-201
98450123452024-02-262024-03-047
99001234562024-03-142024-03-206

How to solve Prepaid Service Lapses Between Recharges

Each recharge is compared with its successor, which is what LEAD gives: partitioned by mobile_number and ordered by recharge_date, LEAD(recharge_date) is the next recharge of the same number, or NULL on the number's last row. The expiry is computed on the same row with DATE_ADD(recharge_date, INTERVAL validity_days DAY).

A lapse is recharged_on > expired_on. The strict comparison keeps a recharge made on the expiry day out of the answer, and the last recharge drops out on its own because a comparison with NULL is never true. DATEDIFF(recharged_on, expired_on) is then the number of days without service. A recharge made while the earlier plan was still running simply produces no lapse for that pair — the rule compares neighbours only.

Without windows, the next recharge is MIN(recharge_date) among the same number's later recharges, a correlated subquery that costs a lookup per row; with an index on (mobile_number, recharge_date) both approaches are fast, but the window version reads the table once.

Reference solution (MySQL)

WITH seq AS (
  SELECT mobile_number,
         DATE_ADD(recharge_date, INTERVAL validity_days DAY) AS expired_on,
         LEAD(recharge_date) OVER (PARTITION BY mobile_number ORDER BY recharge_date) AS recharged_on
  FROM Recharge
)
SELECT mobile_number, expired_on, recharged_on, DATEDIFF(recharged_on, expired_on) AS lapse_days
FROM seq
WHERE recharged_on > expired_on
ORDER BY mobile_number, expired_on, recharged_on

Another way

SELECT mobile_number, expired_on, recharged_on, DATEDIFF(recharged_on, expired_on) AS lapse_days FROM (
  SELECT r.mobile_number, ADDDATE(r.recharge_date, r.validity_days) AS expired_on,
         (SELECT MIN(n.recharge_date) FROM Recharge n
           WHERE n.mobile_number = r.mobile_number AND n.recharge_date > r.recharge_date) AS recharged_on
  FROM Recharge r
) x
WHERE recharged_on IS NOT NULL AND DATEDIFF(recharged_on, expired_on) > 0
ORDER BY mobile_number, expired_on, recharged_on

← Number Port Requests Breaching the 72-Hour SLA · Longest Streak of Heavy Data Days →