The Funnel Table
This is the chart everybody pictures when they hear the word funnel: a bar per step, each shorter than the one above it, showing how many people survived that far. Kindled's growth lead needs the numbers behind it. The measure is people, not actions — somebody who looked at four products is one person who reached the looking step, and counting them four times would make the chart lie about how many shoppers there are.
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 one row per kind of action, with columns event_type and users — how many separate people produced at least one action of that kind. Widest step first; steps with the same headcount are listed alphabetically by event_type.
Example output — shape only, on invented headcounts. The event_type labels are the real stored ones:
| event_type | users |
|---|---|
| signup | 34 |
| view_product | 21 |
| purchase | 13 |
| add_to_cart | 9 |
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