Task
You are given user sign-up data and downstream product interaction events from Chime surfaces such as SpotMe, Pay Anyone, and Savings. Write a PostgreSQL query to return, for each signed-up user, the first feature they touched after sign-up. Only count events that occurred at or after the user's signup_at timestamp, and ignore events where the feature name is NULL. If a user has multiple qualifying events at the exact same timestamp, break ties by the smallest event_id. Include users who signed up but never touched a feature, showing NULL for the feature and event timestamp.
Schema
users
| column | type | description |
|---|
| user_id | INT | Unique user identifier |
| email | VARCHAR(255) | User email |
| signup_at | TIMESTAMP | Account sign-up timestamp |
| acquisition_channel | VARCHAR(50) | Sign-up acquisition source |
feature_events
| column | type | description |
|---|
| event_id | INT | Unique event identifier |
| user_id | INT | User who generated the event |
| feature_name | VARCHAR(100) | Chime feature touched |
| surface_name | VARCHAR(100) | App surface where the event happened |
| event_at | TIMESTAMP | Event timestamp |
Sample data
users
feature_events
| event_id | user_id | feature_name | surface_name | event_at |
|---|
| 2003 | 101 | SpotMe | home_tab | 2024-01-05 09:05:00 |
| 2005 | 102 | Pay Anyone | transfers_tab | 2024-01-05 10:30:00 |
| 2010 | 103 | Savings | savings_tab | 2024-01-06 08:20:00 |
Expected output
| user_id | email | signup_at | first_feature_name | first_surface_name | first_event_at |
|---|
| 101 | ava@example.com | 2024-01-05 09:00:00 | SpotMe | home_tab | 2024-01-05 09:05:00 |
| 102 | ben@example.com | 2024-01-05 10:30:00 | Pay Anyone | transfers_tab | 2024-01-05 10:30:00 |
| 103 | chloe@example.com | 2024-01-06 08:15:00 | Savings | savings_tab | 2024-01-06 08:20:00 |
