Subscriptions Renewing in the Next Seven Days

Easy
478.

Subscriptions Renewing in the Next Seven Days

EasyDates

MySQL

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

ColumnTypeValues
subscription_idint
account_idint
planenumMobile, Basic, Standard, Premium
statusenumactive, paused, cancelled
next_renewaldate

Primary key: subscription_id.

One row per account's subscription (an account has at most one).

Example

The rows must come back in the order shown.

Subscription

subscription_idaccount_idplanstatusnext_renewal
13101Premiumactive2024-06-10
23102Mobileactive2024-06-16
33103Basicpaused2024-06-12
43104Standardactive2024-06-17
53105Mobileactive2024-06-12
63106Premiumcancelled2024-06-11
73107Basicactive2024-06-09

Output

account_idplannext_renewaldays_left
3101Premium2024-06-100
3105Mobile2024-06-122
3102Mobile2024-06-166
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.

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

ColumnType
subscription_idint
account_idint
planenum(Mobile, Basic, Standard, Premium)
statusenum(active, paused, cancelled)
next_renewaldate

Primary key: subscription_id.

One row per account's subscription (an account has at most one).

Examples

Example 1

Subscription

subscription_idaccount_idplanstatusnext_renewal
13101Premiumactive2024-06-10
23102Mobileactive2024-06-16
33103Basicpaused2024-06-12
43104Standardactive2024-06-17
53105Mobileactive2024-06-12
63106Premiumcancelled2024-06-11
73107Basicactive2024-06-09

Output

account_idplannext_renewaldays_left
3101Premium2024-06-100
3105Mobile2024-06-122
3102Mobile2024-06-166

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_id

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

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