The Conversion Rate
One number gets quoted more than any other in Kindled's investor update: the conversion rate — the share of everybody who registered who went on to buy at least once. The denominator is every account, including people who registered and did nothing afterwards, because leaving them out is how a conversion rate gets flattered into meaninglessness. Somebody who bought twice still counts as one buyer.
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 one row and one column, conversion_pct, holding the percentage of registered people who have at least one purchase, rounded to 1 decimal place.
Example output — the shape, on an invented figure. A product converting a little over three fifths of its accounts reads 61.8, not 0.618:
| conversion_pct |
|---|
| 61.8 |
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