Signups Who Never Created a Project — SQL Joins Problem
In a task-tracking app a new user is activated once they create their first project (the project_created event).
- Difficulty: Easy
- Topics: Joins
- Dialect: MySQL
- Problem: #399
Problem statement
In a task-tracking app a new user is activated once they create their first project (the project_created event). Users who only logged in or browsed are not activated.
Return every user who has never fired a project_created event, with the columns user_id and email, ordered by user_id. A user with no events at all is also in the answer.
Tables
Table: users
| Column | Type |
|---|---|
| user_id | int |
| varchar | |
| signed_up_at | datetime |
Primary key: user_id.
One row per person who created an account.
Table: events
| Column | Type |
|---|---|
| event_id | int |
| user_id | int |
| event_name | varchar |
| occurred_at | datetime |
Primary key: event_id.
The product's event log; user_id always names a row of users.
Examples
Example 1
users
| user_id | signed_up_at | |
|---|---|---|
| 11 | aarav@zentrix.io | 2024-08-01 09:10:00 |
| 12 | diya@quillpad.in | 2024-08-01 11:45:00 |
| 13 | kabir@ledgerly.com | 2024-08-02 08:05:00 |
| 14 | meera@vaani.ai | 2024-08-03 17:30:00 |
| 15 | rohan@mintmark.com | 2024-08-03 18:00:00 |
events
| event_id | user_id | event_name | occurred_at |
|---|---|---|---|
| 1 | 11 | login | 2024-08-01 09:11:00 |
| 2 | 11 | project_created | 2024-08-01 09:20:00 |
| 3 | 12 | login | 2024-08-01 11:46:00 |
| 4 | 12 | report_viewed | 2024-08-01 11:50:00 |
| 5 | 14 | project_created | 2024-08-03 17:40:00 |
| 6 | 14 | project_created | 2024-08-04 10:00:00 |
| 7 | 13 | login | 2024-08-02 08:06:00 |
Output
| user_id | |
|---|---|
| 12 | diya@quillpad.in |
| 13 | kabir@ledgerly.com |
| 15 | rohan@mintmark.com |
How to solve Signups Who Never Created a Project
This is an anti join with a condition on the other side. LEFT JOIN events to users, but only the project_created events: the condition e.event_name = 'project_created' goes in the ON clause, so a user whose events are all logins finds no partner and keeps one row of NULLs. Then WHERE e.event_id IS NULL keeps exactly those users.
Putting the event-name test in WHERE instead is the classic slip: the LEFT JOIN would first pair the user with their login rows, and the WHERE would then throw those rows away — the user disappears from the answer, although they were never activated. Users with no events at all survive either way, which is why the mistake hides on small data.
NOT EXISTS with the same two conditions says it directly. NOT IN is safe here because events.user_id is never NULL; if it could be, one NULL in the list would empty the answer. Each form is one index lookup per user on (user_id, event_name).
Reference solution (MySQL)
SELECT u.user_id, u.email
FROM users u
LEFT JOIN events e
ON e.user_id = u.user_id AND e.event_name = 'project_created'
WHERE e.event_id IS NULL
ORDER BY u.user_idAnother way
SELECT user_id, email FROM users u WHERE NOT EXISTS (SELECT 1 FROM events e WHERE e.user_id = u.user_id AND e.event_name = 'project_created') ORDER BY user_idAnother way
SELECT user_id, email FROM users WHERE user_id NOT IN (SELECT user_id FROM events WHERE event_name = 'project_created') ORDER BY user_id← Annual Enterprise Accounts Signed Up in 2024 · Daily Active Users From the Event Log →