You are given user signup data and Chime cash movement events. Write a SQL query to return each signed-up user and the date of their first completed deposit that occurred on or after their signup timestamp. Include users who never made a qualifying deposit, showing NULL for the deposit date. Treat only transactions with transaction_type = 'deposit' and status = 'completed' as valid deposits.
users| column | type | description |
|---|---|---|
| user_id | INT | Unique user identifier |
| VARCHAR(255) | User email | |
| signup_at | TIMESTAMP | Signup timestamp |
| acquisition_channel | VARCHAR(50) | Signup source |
chime_transactions| column | type | description |
|---|---|---|
| transaction_id | INT | Unique transaction identifier |
| user_id | INT | User who initiated the transaction |
| transaction_type | VARCHAR(50) | Type such as deposit, withdrawal, transfer |
| status | VARCHAR(50) | Transaction status |
| amount | NUMERIC(10,2) | Transaction amount |
| transaction_at | TIMESTAMP | Transaction timestamp |
| source_surface | VARCHAR(50) | Product surface where the event originated |
users| user_id | signup_at | |
|---|---|---|
| 101 | ava.lee@example.com | 2024-01-05 09:00:00 |
| 102 | ben.ross@example.com | 2024-01-03 14:30:00 |
| 103 | cara.ng@example.com | 2024-01-10 08:15:00 |
chime_transactions| transaction_id | user_id | transaction_type | status | transaction_at |
|---|---|---|---|---|
| 2001 | 101 | deposit | completed | 2024-01-04 18:00:00 |
| 2002 | 101 | deposit | completed | 2024-01-05 10:00:00 |
| 2005 | 102 | deposit | pending | 2024-01-04 09:00:00 |
| user_id | signup_at | first_deposit_date | |
|---|---|---|---|
| 101 | ava.lee@example.com | 2024-01-05 09:00:00 | 2024-01-05 |
| 102 | ben.ross@example.com | 2024-01-03 14:30:00 | 2024-01-03 |
| 103 | cara.ng@example.com | 2024-01-10 08:15:00 | 2024-01-11 |
| 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 |
|---|---|---|
| transaction_idPK | INT | Unique transaction identifier |
| user_id | INT | User associated with the transaction |
| transaction_type | VARCHAR(50) | Transaction type such as deposit or withdrawal |
| status | VARCHAR(50) | Processing status of the transaction |
| amount | NUMERIC(10,2) | Transaction amount |
| transaction_at | TIMESTAMP | Timestamp when the transaction occurred |
| source_surface | VARCHAR(50) | Chime product surface where the transaction originated |
{"users":[[104,"drew.kim@example.com","2024-01-08 12:00:00","paid_social"],[101,"ava.lee@example.com","2024-01-05 09:00:00","referral"],[108,"hana.cho@example.com","2024-01-04 16:45:00",null],[103,"cara.ng@example.com","2024-01-10 08:15:00","organic"],[106,"finn.ortiz@example.com","2024-01-07 11:20:00","affiliate"],[102,"ben.ross@example.com","2024-01-03 14:30:00","organic"],[107,"gia.patel@example.com","2024-01-02 07:50:00","referral"],[105,"emma.jones@example.com","2024-01-01 00:00:00","paid_sOutput[["1","tessa.moore@example.com","2024-01-06 14:36:01","null"],["2","julia.martin@example.com","2024-01-08 22:33:16","null"],["3","priya.shah@example.com","2024-01-01 07:39:01","null"],["4","emma.jones@example.com","2024-01-09 16:12:44","null"],["5","finn.ortiz@example.com","2024-01-10 10:07:23","null"],["6","mason.hill@example.com","2024-01-04 21:24:26","null"],["7","finn.ortiz@example.com","2024-01-08 12:00:22","null"],["8","yara.cole@example.com","2023-12-31 13:55:44","null"],["9","emma.jones@