Task
Meta wants to measure a simple Messenger onboarding funnel: signup → first message sent → 7-day active. Write a SQL query to calculate, by signup date, how many users reached each stage.
A user counts as:
- signed_up if they appear in
users
- sent_first_message if they sent at least one message on or after signup
- active_7d if they had at least one activity event exactly 7 days after signup
Return one row per signup_date with counts for each funnel stage.
Requirements
- Group results by
signup_date
- Count distinct users at each funnel stage
- A message sent before signup should not count
- A 7-day active event must occur on
signup_date + 7 days
- Order output by
signup_date
Table Definitions
users
| column | type | description |
|---|
| user_id | INT | Unique user identifier |
| signup_ts | TIMESTAMP | Account signup timestamp |
| signup_surface | VARCHAR(50) | Meta signup entry point |
| | |
messages
| column | type | description |
|---|
| message_id | INT | Unique message identifier |
| sender_id | INT | User who sent the message |
| sent_ts | TIMESTAMP | Message send timestamp |
| thread_type | VARCHAR(30) | Conversation type |
| | |
activity_events
| column | type | description |
|---|
| event_id | INT | Unique activity event identifier |
| user_id | INT | Active user |
| event_ts | TIMESTAMP | Activity timestamp |
| event_name | VARCHAR(50) | Activity type |
Sample Data
users
| user_id | signup_ts | signup_surface |
|---|
| 101 | 2024-01-01 09:00:00 | Facebook |
| 102 | 2024-01-01 15:30:00 | Instagram |
| 103 | 2024-01-02 08:00:00 | Facebook |
| 104 | 2024-01-02 11:00:00 | Messenger |
messages
| message_id | sender_id | sent_ts | thread_type |
|---|
| 201 | 101 | 2024-01-01 10:00:00 | direct |
| 202 | 102 | 2023-12-31 23:00:00 | group |
| 203 | 103 | 2024-01-03 09:00:00 | direct |
| 204 | 104 | 2024-01-02 12:00:00 | direct |
activity_events
| event_id | user_id | event_ts | event_name |
|---|
| 301 | 101 | 2024-01-08 08:00:00 | app_open |
| 302 | 102 | 2024-01-08 16:00:00 | app_open |
| 303 | 103 | 2024-01-09 07:30:00 | send_message |
| 304 | 104 | 2024-01-10 09:00:00 | app_open |
Expected Output
| signup_date | signed_up_users | users_sent_first_message | users_active_7d |
|---|
| 2024-01-01 | 2 | 1 | 2 |
| 2024-01-02 | 2 | 2 | 1 |
