Signups Who Never Created a Project

Easy
399.

Signups Who Never Created a Project

EasyJoins

MySQL

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

ColumnType
user_idint
emailvarchar
signed_up_atdatetime

Primary key: user_id.

One row per person who created an account.

Table: events

ColumnType
event_idint
user_idint
event_namevarchar
occurred_atdatetime

Primary key: event_id.

The product's event log; user_id always names a row of users.

Example

The rows must come back in the order shown.

users

user_idemailsigned_up_at
11aarav@zentrix.io2024-08-01 09:10:00
12diya@quillpad.in2024-08-01 11:45:00
13kabir@ledgerly.com2024-08-02 08:05:00
14meera@vaani.ai2024-08-03 17:30:00
15rohan@mintmark.com2024-08-03 18:00:00

events

event_iduser_idevent_nameoccurred_at
111login2024-08-01 09:11:00
211project_created2024-08-01 09:20:00
312login2024-08-01 11:46:00
412report_viewed2024-08-01 11:50:00
514project_created2024-08-03 17:40:00
614project_created2024-08-04 10:00:00
713login2024-08-02 08:06:00

Output

user_idemail
12diya@quillpad.in
13kabir@ledgerly.com
15rohan@mintmark.com
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
user_idint
emailvarchar
signed_up_atdatetime

Primary key: user_id.

One row per person who created an account.

Table: events

ColumnType
event_idint
user_idint
event_namevarchar
occurred_atdatetime

Primary key: event_id.

The product's event log; user_id always names a row of users.

Examples

Example 1

users

user_idemailsigned_up_at
11aarav@zentrix.io2024-08-01 09:10:00
12diya@quillpad.in2024-08-01 11:45:00
13kabir@ledgerly.com2024-08-02 08:05:00
14meera@vaani.ai2024-08-03 17:30:00
15rohan@mintmark.com2024-08-03 18:00:00

events

event_iduser_idevent_nameoccurred_at
111login2024-08-01 09:11:00
211project_created2024-08-01 09:20:00
312login2024-08-01 11:46:00
412report_viewed2024-08-01 11:50:00
514project_created2024-08-03 17:40:00
614project_created2024-08-04 10:00:00
713login2024-08-02 08:06:00

Output

user_idemail
12diya@quillpad.in
13kabir@ledgerly.com
15rohan@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_id

Another 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_id

Another 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 →