Engagement, With Names
The engagement table Kindled's growth lead circulates each week is read by people who have never seen the database, so a column of id numbers is useless to them. The activity is tracked against ids and the names live on the account records, so the two have to be brought together. Anybody who has produced no tracked action at all has nothing to report and stays off the table.
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 per person who has produced at least one tracked action, with columns name and events — how many actions that person produced. Busiest first; people on the same count are listed alphabetically by name.
Example output — shape only, on invented people. The first two are level, so they fall alphabetically:
| name | events |
|---|---|
| Nadia Petrov | 16 |
| Owen Blackwood | 16 |
| Rafael Costa | 13 |
| Sunny Oduya | 11 |
| Tessa Lund | 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