Where the Funnel Leaks
The steepest fall in Kindled's funnel is the last one: the gap between putting something in a basket and paying for it. Before anyone argues about why, the growth lead wants the two headcounts sitting on a single line so the leak is visible at a glance — how many people reached a basket, and how many of them got as far as paying. Both figures count people, so a shopper who carted three times is one person.
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 |
The table already exists in the database — there is nothing to create or load.
Task: Write a query that returns exactly one row with two columns, carted and purchased — how many separate people have at least one basket action, and how many have at least one purchase.
Example output — the shape, on invented figures. One row, two columns, side by side rather than stacked:
| carted | purchased |
|---|---|
| 26 | 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