Time to Purchase
Kindled wants to know how long a new shopper takes to decide. The figure is the gap between the day somebody registered and the day of their first purchase, which the growth lead will use to set how long the welcome-email sequence should run. Registering and buying are recorded in two different tables, and a shopper who has bought more than once converted on the first of those purchases, not the latest. People who have never bought have no gap to measure and stay out of the report.
events — one row per tracked action. user_id points at a row in users. event_type is one of signup, view_product, add_to_cart and purchase. event_date is the day the action happened. Nothing guarantees a person produces all four kinds, or any particular number of rows.
| 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 |
users — one row per person who has created an account. source records how they arrived: organic means they found Kindled themselves, paid_search means they clicked a bought advert, and referral means an existing customer invited them. signup_date is the day they registered.
| 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 |
Both tables already exist in the database — there is nothing to create or load.
Task: Write a query that returns two columns, name and days_to_purchase, one row per person who has bought at least once, days_to_purchase being the whole number of days from their signup date to their earliest purchase. Fastest first; equal gaps are listed alphabetically by name.
Example output — shape only, on invented people. Somebody who bought the day they signed up shows a gap of 0, and that sorts to the top:
| name | days_to_purchase |
|---|---|
| Nadia Petrov | 0 |
| Owen Blackwood | 5 |
| Rafael Costa | 11 |
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