Uber: Uber One Trials Ending Without Auto-Renew

Easy
973.

Uber: Uber One Trials Ending Without Auto-Renew

EasyBasicsDates

MySQL

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

ColumnTypeValues
membership_idint
rider_namevarchar
planenumtrial, monthly, annual
started_ondate
ends_ondate
auto_renewbool

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.

Example

The rows must come back in the order shown.

UberOneMembership

membership_idrider_nameplanstarted_onends_onauto_renew
1Aaravtrial2024-06-012024-07-010
2Diyatrial2024-06-052024-07-051
3Kabirmonthly2024-06-032024-07-030
4Meeratrial2024-06-072024-07-070
5Rohantrial2024-06-082024-07-080
6Zaratrial2024-05-312024-06-300
7Ishaantrial2024-06-022024-07-020

Output

membership_idrider_nameends_on
1Aarav2024-07-01
7Ishaan2024-07-02
4Meera2024-07-07
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.

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

ColumnType
membership_idint
rider_namevarchar
planenum(trial, monthly, annual)
started_ondate
ends_ondate
auto_renewbool

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_idrider_nameplanstarted_onends_onauto_renew
1Aaravtrial2024-06-012024-07-010
2Diyatrial2024-06-052024-07-051
3Kabirmonthly2024-06-032024-07-030
4Meeratrial2024-06-072024-07-070
5Rohantrial2024-06-082024-07-080
6Zaratrial2024-05-312024-06-300
7Ishaantrial2024-06-022024-07-020

Output

membership_idrider_nameends_on
1Aarav2024-07-01
7Ishaan2024-07-02
4Meera2024-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_id

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