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
| Column | Type |
|---|---|
| recharge_id | int |
| mobile_number | varchar |
| recharge_date | date |
| validity_days | int |
Primary key: recharge_id.
A number recharges at most once a day.
Examples
Example 1
Recharge
| recharge_id | mobile_number | recharge_date | validity_days |
|---|---|---|---|
| 1 | 9845012345 | 2024-01-01 | 28 |
| 2 | 9845012345 | 2024-01-29 | 28 |
| 3 | 9845012345 | 2024-03-04 | 56 |
| 4 | 9900123456 | 2024-01-10 | 84 |
| 5 | 9900123456 | 2024-02-15 | 28 |
| 6 | 9900123456 | 2024-03-20 | 28 |
| 7 | 8123456789 | 2024-02-01 | 28 |
| 8 | 7019988776 | 2024-01-05 | 14 |
| 9 | 7019988776 | 2024-01-20 | 28 |
Output
| mobile_number | expired_on | recharged_on | lapse_days |
|---|---|---|---|
| 7019988776 | 2024-01-19 | 2024-01-20 | 1 |
| 9845012345 | 2024-02-26 | 2024-03-04 | 7 |
| 9900123456 | 2024-03-14 | 2024-03-20 | 6 |
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_onAnother 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 →