The Engagement Count
Kindled's growth lead wants a rough engagement measure before building anything more careful: how much each shopper did. Every tracked action is a row, so the number of rows a person produced is a crude but instant proxy for how far into the funnel they got. The report is for the analytics team, so internal ids are fine and names are not needed.
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 person who produced at least one tracked action, with columns user_id and events, events being how many actions that person produced. Busiest first; people on the same count are listed with the smaller user_id first.
Example output — shape only, on invented people. The first two are level, so the smaller user_id goes first:
| user_id | events |
|---|---|
| 21 | 16 |
| 24 | 16 |
| 27 | 13 |
| 29 | 11 |
| 32 | 10 |
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