Subscriptions Renewing in the Next Seven Days — SQL Problem
Before an auto-debit, the app sends a UPI mandate reminder. Today is 2024-06-10.
- Difficulty: Easy
- Topics: Dates
- Dialect: MySQL
- Problem: #478
Problem statement
Before an auto-debit, the app sends a UPI mandate reminder. Today is 2024-06-10. A subscription needs a reminder when its status is active and its next_renewal falls between 2024-06-10 and 2024-06-16, both included.
Return the columns account_id, plan, next_renewal and days_left (the number of days from 2024-06-10 to next_renewal; 0 for a renewal today). Paused and cancelled subscriptions get no reminder. Order the rows by days_left, then by account_id.
Tables
Table: Subscription
| Column | Type |
|---|---|
| subscription_id | int |
| account_id | int |
| plan | enum(Mobile, Basic, Standard, Premium) |
| status | enum(active, paused, cancelled) |
| next_renewal | date |
Primary key: subscription_id.
One row per account's subscription (an account has at most one).
Examples
Example 1
Subscription
| subscription_id | account_id | plan | status | next_renewal |
|---|---|---|---|---|
| 1 | 3101 | Premium | active | 2024-06-10 |
| 2 | 3102 | Mobile | active | 2024-06-16 |
| 3 | 3103 | Basic | paused | 2024-06-12 |
| 4 | 3104 | Standard | active | 2024-06-17 |
| 5 | 3105 | Mobile | active | 2024-06-12 |
| 6 | 3106 | Premium | cancelled | 2024-06-11 |
| 7 | 3107 | Basic | active | 2024-06-09 |
Output
| account_id | plan | next_renewal | days_left |
|---|---|---|---|
| 3101 | Premium | 2024-06-10 | 0 |
| 3105 | Mobile | 2024-06-12 | 2 |
| 3102 | Mobile | 2024-06-16 | 6 |
How to solve Subscriptions Renewing in the Next Seven Days
The window is seven calendar days starting today. next_renewal BETWEEN '2024-06-10' AND DATE_ADD('2024-06-10', INTERVAL 6 DAY) covers it — BETWEEN includes both ends, so a renewal today and one on the 16th both qualify while the 17th does not. An equivalent half-open form is >= '2024-06-10' AND < '2024-06-17', which is the safer habit when the column is a datetime with a time part.
DATEDIFF(next_renewal, '2024-06-10') counts the days left: 0 for today, 6 for the last day. Mind the argument order — MySQL's DATEDIFF(a, b) is a - b. The status filter drops paused and cancelled subscriptions, and a renewal date already in the past (the 9th) falls outside the window.
Ordering by days_left and by next_renewal is the same thing, since one is the other minus a constant; account_id breaks ties between renewals on the same day. An index on next_renewal turns this into a short range scan.
Reference solution (MySQL)
SELECT account_id, plan, next_renewal, DATEDIFF(next_renewal, '2024-06-10') AS days_left
FROM Subscription
WHERE status = 'active'
AND next_renewal BETWEEN '2024-06-10' AND DATE_ADD('2024-06-10', INTERVAL 6 DAY)
ORDER BY days_left, account_idAnother way
SELECT account_id, plan, next_renewal, DATEDIFF(next_renewal, '2024-06-10') AS days_left FROM Subscription WHERE status = 'active' AND DATEDIFF(next_renewal, '2024-06-10') BETWEEN 0 AND 6 ORDER BY next_renewal, account_idAnother way
SELECT account_id, plan, next_renewal, TIMESTAMPDIFF(DAY, '2024-06-10', next_renewal) AS days_left FROM Subscription WHERE status = 'active' AND next_renewal >= '2024-06-10' AND next_renewal < '2024-06-17' ORDER BY days_left, account_id← Tracks With a Featured Artist · Short, Standard and Long Episodes →