Task
NovaApp wants to understand which product feature each user touches first after creating an account. Write a SQL query to return the first feature interaction that happened on or after each user's signup timestamp.
Requirements
- Return one row per user who has at least one valid interaction after signup.
- Include
user_id, email, signup_at, feature_name, and first_interaction_at.
- Ignore events that occurred before signup.
- If a user has multiple valid events at the exact same timestamp, return the one with the smaller
event_id.
- Order the final result by
user_id.
Table Definitions
users
| column | type | description |
|---|
| user_id | INT | Primary key for each user |
| email | VARCHAR(255) | User email address |
| signup_at | TIMESTAMP | Account creation timestamp |
| plan_tier | VARCHAR(50) | Subscription tier |
feature_events
| column | type | description |
|---|
| event_id | INT | Primary key for each event |
| user_id | INT | User who triggered the event |
| feature_name | VARCHAR(100) | Product feature used |
| event_at | TIMESTAMP | Event timestamp |
| device_type | VARCHAR(50) | Device used for the event |
Sample Data
users
feature_events
| event_id | user_id | feature_name | event_at | device_type |
|---|
| 1005 | 101 | search | 2024-01-10 09:05:00 | web |
| 1002 | 101 | onboarding_checklist | 2024-01-10 09:05:00 | mobile |
| 1008 | 102 | dashboard | 2024-01-10 10:20:00 | web |
Expected Output
| user_id | email | signup_at | feature_name | first_interaction_at |
|---|
| 101 | ava@novaapp.com | 2024-01-10 09:00:00 | onboarding_checklist | 2024-01-10 09:05:00 |
| 102 | ben@novaapp.com | 2024-01-10 10:30:00 | upload_file | 2024-01-10 10:30:00 |
