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.
users| column | type | description |
|---|---|---|
| user_id | INT | Unique user identifier |
| 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 |
users| user_id | signup_at | acquisition_channel | |
|---|---|---|---|
| 101 | ava@example.com | 2024-01-05 09:00:00 | paid_search |
| 102 | ben@example.com | 2024-01-05 10:30:00 | referral |
| 103 | chloe@example.com | 2024-01-06 08:15:00 | organic |
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 |
| user_id | 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 |
| Column | Type | Description |
|---|---|---|
| user_idPK | INT | Unique user identifier |
| VARCHAR(255) | User email address | |
| signup_at | TIMESTAMP | Timestamp when the user signed up |
| acquisition_channel | VARCHAR(50) | Marketing or referral source for signup |
| Column | Type | Description |
|---|---|---|
| event_idPK | INT | Unique event identifier |
| user_id | INT | User who generated the event |
| feature_name | VARCHAR(100) | Feature the user interacted with |
| surface_name | VARCHAR(100) | App surface where the event occurred |
| event_at | TIMESTAMP | Timestamp of the feature event |
{"users":[[101,"ava@example.com","2024-01-05 09:00:00","paid_search"],[102,"ben@example.com","2024-01-05 10:30:00","referral"],[103,"chloe@example.com","2024-01-06 08:15:00","organic"],[104,"dan@example.com","2024-01-06 12:00:00","affiliate"],[105,"emma@example.com","2024-01-07 07:45:00","paid_social"],[106,"felix@example.com","2024-01-07 09:20:00",null],[107,"grace@example.com","2024-01-08 14:10:00","organic"],[108,"hugo@example.com","2024-01-08 16:00:00","referral"],[109,"ivy@example.com","202Output[["1","zane@example.com","2024-01-12 09:32:36","null","null","null"],["2","noah@example.com","2024-01-05 21:27:03","null","null","null"],["3","xavier@example.com","2024-01-05 10:32:16","null","null","null"],["4","chloe@example.com","2024-01-11 14:57:48","null","null","null"],["5","zane@example.com","2024-01-11 19:00:09","null","null","null"],["6","victor@example.com","2024-01-05 21:00:23","null","null","null"],["7","peter@example.com","2024-01-09 00:58:25","null","null","null"],["8","willa@examp