All problems

Who Signed Up but Never Looked Around?

mediumSQLSubqueriesSet Logic

Kindled's growth team has a suspicion about the top of the funnel. Some people finish signing up, do nothing at all, and are never seen again — they never so much as look at a product — and if that group is large the problem is the first screen after signup rather than the ads bringing people in. Before anything can be fixed, the team wants the names.

The difficulty is that the evidence for this is an absence. The event log records the things people did; nobody writes a row saying "did not browse". So the answer is the set of signed-up users for which a particular kind of row is missing, and missing rows cannot be filtered for directly.

users — one row per signed-up person. source is the channel that brought them in.

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

events — one row per action a user took. event_type is the kind of action; each user's first row is their signup.

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

Both tables already exist in the database — there is nothing to create or load.

Task: Write a query returning a single column, name, holding every user with no view_product row anywhere in the event log. A user who viewed a product once and never returned does not qualify — one view is enough to be excluded. Hand the names back in alphabetical order.

Example output — shape only, on invented names.

name
Bram Visser
Lucia Ferreira

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

Discussion

Sign in to join the discussion — reading is open to everyone.

Loading comments…