Free Trials Ending Within a Week

Easy
404.

Free Trials Ending Within a Week

EasyDatesBasics

MySQL

Every Monday the lifecycle team emails workspaces whose 14-day trial is about to run out. Today is 2024-06-10. A trial is in the campaign when it has not converted (converted_on is NULL) and its ends_on is from today up to and including 2024-06-17.

Return trial_id, workspace_name and days_left (whole days from 2024-06-10 to ends_on, so a trial ending today has 0), ordered by days_left, then trial_id.

Tables

Table: trials

ColumnType
trial_idint
workspace_namevarchar
started_ondate
ends_ondate
converted_ondate

Primary key: trial_id.

One row per trial; converted_on is the day the workspace started paying, or NULL if it has not.

Example

The rows must come back in the order shown.

trials

trial_idworkspace_namestarted_onends_onconverted_on
1Zentrix Labs2024-05-272024-06-10NULL
2Quillpad2024-05-302024-06-13NULL
3Ledgerly2024-05-302024-06-132024-06-05
4Orbitpay2024-06-032024-06-17NULL
5Vaani AI2024-06-042024-06-18NULL
6Tiffinly2024-05-262024-06-09NULL
7Mintmark2024-05-312024-06-14NULL

Output

trial_idworkspace_namedays_left
1Zentrix Labs0
2Quillpad3
7Mintmark4
4Orbitpay7
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.

Free Trials Ending Within a Week — SQL Dates Problem

Every Monday the lifecycle team emails workspaces whose 14-day trial is about to run out. Today is 2024-06-10.

  • Difficulty: Easy
  • Topics: Dates, Basics
  • Dialect: MySQL
  • Problem: #404

Problem statement

Every Monday the lifecycle team emails workspaces whose 14-day trial is about to run out. Today is 2024-06-10. A trial is in the campaign when it has not converted (converted_on is NULL) and its ends_on is from today up to and including 2024-06-17.

Return trial_id, workspace_name and days_left (whole days from 2024-06-10 to ends_on, so a trial ending today has 0), ordered by days_left, then trial_id.

Tables

Table: trials

ColumnType
trial_idint
workspace_namevarchar
started_ondate
ends_ondate
converted_ondate

Primary key: trial_id.

One row per trial; converted_on is the day the workspace started paying, or NULL if it has not.

Examples

Example 1

trials

trial_idworkspace_namestarted_onends_onconverted_on
1Zentrix Labs2024-05-272024-06-10NULL
2Quillpad2024-05-302024-06-13NULL
3Ledgerly2024-05-302024-06-132024-06-05
4Orbitpay2024-06-032024-06-17NULL
5Vaani AI2024-06-042024-06-18NULL
6Tiffinly2024-05-262024-06-09NULL
7Mintmark2024-05-312024-06-14NULL

Output

trial_idworkspace_namedays_left
1Zentrix Labs0
2Quillpad3
7Mintmark4
4Orbitpay7

How to solve Free Trials Ending Within a Week

Two filters and one computed column. converted_on IS NULL keeps the trials that have not paid — = NULL would match nothing, because a comparison with NULL is unknown. The window is today through seven days from now, both inclusive, so ends_on BETWEEN '2024-06-10' AND '2024-06-17'; a trial that ended yesterday is too late for the email and one ending on the 18th waits for next week.

DATEDIFF(ends_on, '2024-06-10') gives whole days remaining, 0 for a trial ending today. Filtering on that difference being between 0 and 7 is equivalent; comparing the column directly is the index-friendly form. TIMESTAMPDIFF(DAY, a, b) is the same count with the arguments in the other order — a frequent source of negative numbers.

Days left repeat, so trial_id breaks ties. One scan of the table.

Reference solution (MySQL)

SELECT trial_id, workspace_name, DATEDIFF(ends_on, '2024-06-10') AS days_left
FROM trials
WHERE converted_on IS NULL
  AND ends_on BETWEEN '2024-06-10' AND '2024-06-17'
ORDER BY days_left, trial_id

Another way

SELECT trial_id, workspace_name, DATEDIFF(ends_on, '2024-06-10') AS days_left FROM trials WHERE converted_on IS NULL AND DATEDIFF(ends_on, '2024-06-10') BETWEEN 0 AND 7 ORDER BY days_left, trial_id

Another way

SELECT trial_id, workspace_name, TIMESTAMPDIFF(DAY, '2024-06-10', ends_on) AS days_left FROM trials WHERE converted_on IS NULL AND ends_on >= '2024-06-10' AND ends_on <= DATE_ADD('2024-06-10', INTERVAL 7 DAY) ORDER BY days_left, trial_id

← Active Subscriptions Above the Average MRR · Feature Adoption, Including Features Nobody Used →