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
| Column | Type |
|---|---|
| trial_id | int |
| workspace_name | varchar |
| started_on | date |
| ends_on | date |
| converted_on | date |
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_id | workspace_name | started_on | ends_on | converted_on |
|---|---|---|---|---|
| 1 | Zentrix Labs | 2024-05-27 | 2024-06-10 | NULL |
| 2 | Quillpad | 2024-05-30 | 2024-06-13 | NULL |
| 3 | Ledgerly | 2024-05-30 | 2024-06-13 | 2024-06-05 |
| 4 | Orbitpay | 2024-06-03 | 2024-06-17 | NULL |
| 5 | Vaani AI | 2024-06-04 | 2024-06-18 | NULL |
| 6 | Tiffinly | 2024-05-26 | 2024-06-09 | NULL |
| 7 | Mintmark | 2024-05-31 | 2024-06-14 | NULL |
Output
| trial_id | workspace_name | days_left |
|---|---|---|
| 1 | Zentrix Labs | 0 |
| 2 | Quillpad | 3 |
| 7 | Mintmark | 4 |
| 4 | Orbitpay | 7 |
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_idAnother 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_idAnother 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 →