How Long Have Customers Been Ordering?
The lifecycle marketing team at Whetcode Supply is deciding when a win-back email should fire, and to set that clock they first want to know how long each customer has actually been an active buyer: the stretch of time from their very first order to their most recent one.
The catch is that a customer's span depends on two rows of the orders table that nobody has picked out for you, and one customer here has ordered exactly once — their first order and their last order are the same row. Dates are stored as text as well, so a plain subtraction is not available.
orders — one row per order. customer_id is the customer who placed it. order_date is the date it was placed, stored as text in YYYY-MM-DD form.
| id | customer_id | product_id | quantity | order_date |
|---|---|---|---|---|
| 1 | 1 | 1 | 2 | 2023-02-01 |
| 2 | 1 | 4 | 1 | 2023-02-01 |
| 3 | 2 | 2 | 1 | 2023-02-10 |
| 4 | 3 | 3 | 3 | 2023-03-05 |
| 5 | 4 | 5 | 5 | 2023-03-11 |
| 6 | 1 | 3 | 1 | 2023-04-02 |
| 7 | 5 | 1 | 4 | 2023-05-20 |
| 8 | 2 | 5 | 2 | 2023-05-22 |
| 9 | 3 | 4 | 1 | 2023-06-01 |
| 10 | 5 | 2 | 1 | 2023-06-15 |
The table already exists in the database — there is nothing to create or load.
Task: Write a query returning two columns, customer_id and days_active: the whole number of days between that customer's earliest order date and their latest. A customer who has ordered only once gets 0, not an absent row. A customer who has never ordered at all does not appear. Rows come back sorted by customer_id, smallest first.
Example output — shape only, on invented customers; customer 7 ordered once and only once, which is why the figure is 0 rather than missing.
| customer_id | days_active |
|---|---|
| 6 | 143 |
| 7 | 0 |
| 9 | 58 |
Sign in to solve this problem
Reading problems is free for everyone — solving them (Run, Submit, and tracking what you've solved) needs an account.
Sign in