Uber: Uber One Trials Ending Without Auto-Renew
The Uber One lifecycle team sends a "your free trial is ending" nudge to members who have not switched auto-renew on, so that they do not lose their…
- Difficulty: Easy
- Topics: Basics, Dates
- Dialect: MySQL
- Problem: #973
Problem statement
The Uber One lifecycle team sends a "your free trial is ending" nudge to members who have not switched auto-renew on, so that they do not lose their benefits by surprise.
Return every membership on the trial plan whose ends_on falls between 2024-07-01 and 2024-07-07 inclusive and whose auto_renew is 0. Return the columns membership_id, rider_name and ends_on, ordered by ends_on and then by membership_id.
A practice scenario set at Uber: the tables and data are invented, and this is not a question from, or endorsed by, Uber.
Tables
Table: UberOneMembership
| Column | Type |
|---|---|
| membership_id | int |
| rider_name | varchar |
| plan | enum(trial, monthly, annual) |
| started_on | date |
| ends_on | date |
| auto_renew | bool |
Primary key: membership_id.
One row per membership. auto_renew is 1 when the rider has agreed to be charged when the current plan ends, 0 otherwise.
Examples
Example 1
UberOneMembership
| membership_id | rider_name | plan | started_on | ends_on | auto_renew |
|---|---|---|---|---|---|
| 1 | Aarav | trial | 2024-06-01 | 2024-07-01 | 0 |
| 2 | Diya | trial | 2024-06-05 | 2024-07-05 | 1 |
| 3 | Kabir | monthly | 2024-06-03 | 2024-07-03 | 0 |
| 4 | Meera | trial | 2024-06-07 | 2024-07-07 | 0 |
| 5 | Rohan | trial | 2024-06-08 | 2024-07-08 | 0 |
| 6 | Zara | trial | 2024-05-31 | 2024-06-30 | 0 |
| 7 | Ishaan | trial | 2024-06-02 | 2024-07-02 | 0 |
Output
| membership_id | rider_name | ends_on |
|---|---|---|
| 1 | Aarav | 2024-07-01 |
| 7 | Ishaan | 2024-07-02 |
| 4 | Meera | 2024-07-07 |
How to solve Uber: Uber One Trials Ending Without Auto-Renew
This is a single-table filter with a fixed order. A membership qualifies only when all three facts are true at once, so the conditions are joined with AND: the plan is trial (a monthly member whose plan ends is renewing, not converting), auto_renew is 0 (members who already opted in need no nudge), and ends_on lies in the first week of July.
BETWEEN '2024-07-01' AND '2024-07-07' is inclusive at both ends, which is what the statement asks for. Dates in YYYY-MM-DD form compare correctly, so the alternative's half-open range >= '2024-07-01' AND < '2024-07-08' is the same test — a form that stays right even if the column later gains a time of day.
Several trials can end on the same day, so ordering by ends_on alone would leave their order to the engine; adding membership_id as the second key makes the output fully determined. The query is one scan, or a range read with an index on ends_on.
Reference solution (MySQL)
SELECT membership_id, rider_name, ends_on
FROM UberOneMembership
WHERE plan = 'trial'
AND auto_renew = 0
AND ends_on BETWEEN '2024-07-01' AND '2024-07-07'
ORDER BY ends_on, membership_idAnother way
SELECT membership_id, rider_name, ends_on FROM UberOneMembership WHERE plan IN ('trial') AND NOT auto_renew AND ends_on >= '2024-07-01' AND ends_on < '2024-07-08' ORDER BY 3, 1← Swiggy: Instamart Picking Bags From Order Lines · Uber: Rider Cancellations That Earn a Late-Cancel Fee →