How Fast Do People Buy After Signing Up?
Kindled is about to schedule a "you left something behind" reminder email and has to pick the delay. Send it too early and it lands on people who were always going to buy anyway; send it too late and they have gone. So the team wants one number to anchor the decision: on average, how long the people who did buy took to get there after signing up.
Only buyers count. Five of the eight users never purchased and have no purchase event at all, and including them would drag the figure towards a number that describes nobody. The two dates you need also sit in different tables — the sign-up date is a fact about the user, the purchase date is a fact about an event — and both are stored as text rather than as dates.
users — one row per user. signup_date is the day they signed up, as text in YYYY-MM-DD form.
| id | name | source | signup_date |
|---|---|---|---|
| 1 | Aiden Cole | organic | 2023-05-01 |
| 2 | Bianca Reyes | paid_search | 2023-05-02 |
| 3 | Carlos Mora | organic | 2023-05-03 |
| 4 | Delia Frank | referral | 2023-05-04 |
| 5 | Ewan Blake | paid_search | 2023-05-05 |
| 6 | Fiona Grey | organic | 2023-05-06 |
| 7 | Gus Herrera | referral | 2023-05-07 |
| 8 | Hana Ito | paid_search | 2023-05-08 |
events — one row per action. event_type is one of signup, view_product, add_to_cart or purchase. event_date is the day the action happened.
| id | user_id | event_type | event_date |
|---|---|---|---|
| 1 | 1 | signup | 2023-05-01 |
| 2 | 1 | view_product | 2023-05-01 |
| 3 | 1 | add_to_cart | 2023-05-02 |
| 4 | 1 | purchase | 2023-05-03 |
| 5 | 2 | signup | 2023-05-02 |
| 6 | 2 | view_product | 2023-05-02 |
| 7 | 3 | signup | 2023-05-03 |
| 8 | 3 | view_product | 2023-05-03 |
| 9 | 3 | add_to_cart | 2023-05-04 |
| 10 | 4 | signup | 2023-05-04 |
| 11 | 4 | view_product | 2023-05-05 |
| 12 | 4 | add_to_cart | 2023-05-05 |
| 13 | 4 | purchase | 2023-05-06 |
| 14 | 5 | signup | 2023-05-05 |
| 15 | 6 | signup | 2023-05-06 |
| 16 | 6 | view_product | 2023-05-06 |
| 17 | 7 | signup | 2023-05-07 |
| 18 | 7 | view_product | 2023-05-07 |
| 19 | 7 | add_to_cart | 2023-05-08 |
| 20 | 7 | purchase | 2023-05-09 |
| 21 | 8 | signup | 2023-05-08 |
Both tables already exist in the database — there is nothing to create or load.
Task: Write a query returning exactly one row with a single column, avg_days_to_purchase: the mean number of days from a buyer's signup_date in the users table to the date of their purchase event, averaged across purchase events only. Users who never purchased are left out of the average entirely rather than counted as zeros. The answer is not rounded.
Example output — shape only. If buyers took five and three quarter days on average to get there, the single row would read:
| avg_days_to_purchase |
|---|
| 5.75 |
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