Prepaid Plans Expiring in the First Week of June
The retention team sends a renewal SMS to every prepaid number whose plan runs out soon.
- Difficulty: Easy
- Topics: Basics, Dates
- Dialect: MySQL
- Problem: #498
Problem statement
The retention team sends a renewal SMS to every prepaid number whose plan runs out soon. A plan bought on recharge_date with a validity of validity_days days expires on recharge_date plus validity_days days.
Return every recharge whose expiry date falls between 2024-06-01 and 2024-06-07, both included, with the columns recharge_id, mobile_number and expiry_date. Order the rows by expiry_date, then by recharge_id.
Tables
Table: Recharge
| Column | Type |
|---|---|
| recharge_id | int |
| mobile_number | varchar |
| plan_name | varchar |
| amount | int |
| recharge_date | date |
| validity_days | int |
Primary key: recharge_id.
One row per prepaid recharge; amount is in rupees. A number can recharge more than once.
Examples
Example 1
Recharge
| recharge_id | mobile_number | plan_name | amount | recharge_date | validity_days |
|---|---|---|---|---|---|
| 1 | 9845012345 | Unlimited 28 | 299 | 2024-05-04 | 28 |
| 2 | 9900123456 | Unlimited 84 | 719 | 2024-03-15 | 84 |
| 3 | 9731122334 | Data Sachet 14 | 98 | 2024-05-17 | 14 |
| 4 | 9845012345 | Unlimited 56 | 579 | 2024-04-08 | 56 |
| 5 | 8123456789 | Unlimited 28 | 299 | 2024-05-11 | 28 |
| 6 | 7019988776 | Unlimited 28 | 299 | 2024-05-08 | 28 |
Output
| recharge_id | mobile_number | expiry_date |
|---|---|---|
| 1 | 9845012345 | 2024-06-01 |
| 4 | 9845012345 | 2024-06-03 |
| 6 | 7019988776 | 2024-06-05 |
| 2 | 9900123456 | 2024-06-07 |
How to solve Prepaid Plans Expiring in the First Week of June
The expiry date is derived, not stored: it is the recharge date moved forward by the plan's validity. DATE_ADD(recharge_date, INTERVAL validity_days DAY) computes it per row, and because the interval amount may be a column, one expression serves every plan length — 14, 28, 56 or 84 days.
The filter is then a closed range on that expression. BETWEEN '2024-06-01' AND '2024-06-07' includes both ends, so a plan expiring on the 1st or the 7th is reminded and one expiring on 31 May or 8 June is not. The order is fixed by the statement, with recharge_id breaking ties between plans that expire on the same day.
An equivalent filter avoids computing the date in the WHERE clause: the expiry is on or after the 1st exactly when DATEDIFF('2024-06-01', recharge_date) <= validity_days, and on or before the 7th when DATEDIFF('2024-06-07', recharge_date) >= validity_days. Either way it is one scan of the table; an index on recharge_date cannot help directly, because the condition depends on two columns.
Reference solution (MySQL)
SELECT recharge_id, mobile_number,
DATE_ADD(recharge_date, INTERVAL validity_days DAY) AS expiry_date
FROM Recharge
WHERE DATE_ADD(recharge_date, INTERVAL validity_days DAY) BETWEEN '2024-06-01' AND '2024-06-07'
ORDER BY expiry_date, recharge_idAnother way
SELECT recharge_id, mobile_number, ADDDATE(recharge_date, validity_days) AS expiry_date
FROM Recharge
WHERE DATEDIFF('2024-06-01', recharge_date) <= validity_days
AND DATEDIFF('2024-06-07', recharge_date) >= validity_days
ORDER BY expiry_date, recharge_id← Daily Streams in an Album's First Week, Zero Days Included · Dormant Prepaid Subscribers Since March →