Prepaid Plans Expiring in the First Week of June

Easy
498.

Prepaid Plans Expiring in the First Week of June

EasyBasicsDates

MySQL

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

ColumnType
recharge_idint
mobile_numbervarchar
plan_namevarchar
amountint
recharge_datedate
validity_daysint

Primary key: recharge_id.

One row per prepaid recharge; amount is in rupees. A number can recharge more than once.

Example

The rows must come back in the order shown.

Recharge

recharge_idmobile_numberplan_nameamountrecharge_datevalidity_days
19845012345Unlimited 282992024-05-0428
29900123456Unlimited 847192024-03-1584
39731122334Data Sachet 14982024-05-1714
49845012345Unlimited 565792024-04-0856
58123456789Unlimited 282992024-05-1128
67019988776Unlimited 282992024-05-0828

Output

recharge_idmobile_numberexpiry_date
198450123452024-06-01
498450123452024-06-03
670199887762024-06-05
299001234562024-06-07
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 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

ColumnType
recharge_idint
mobile_numbervarchar
plan_namevarchar
amountint
recharge_datedate
validity_daysint

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_idmobile_numberplan_nameamountrecharge_datevalidity_days
19845012345Unlimited 282992024-05-0428
29900123456Unlimited 847192024-03-1584
39731122334Data Sachet 14982024-05-1714
49845012345Unlimited 565792024-04-0856
58123456789Unlimited 282992024-05-1128
67019988776Unlimited 282992024-05-0828

Output

recharge_idmobile_numberexpiry_date
198450123452024-06-01
498450123452024-06-03
670199887762024-06-05
299001234562024-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_id

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