Task
On Meta Marketplace, the analytics team wants to identify users whose second completed transaction happened within 24 hours of their first completed transaction.
Write a SQL query to return the users who meet this condition.
Requirements
- Consider only rows where
status = 'completed'.
- For each user, identify their first and second completed transactions by
transaction_time.
- Return only users whose second completed transaction occurred within 24 hours of their first.
- Output
user_id, first_transaction_time, second_transaction_time, and the time difference as hours_between.
- Order the final result by
user_id.
Table Definitions
marketplace_transactions
| column | type | description |
|---|
| transaction_id | INT | Unique transaction ID |
| user_id | INT | Meta user ID |
| transaction_time | TIMESTAMP | Time the transaction was created |
| amount | DECIMAL(10,2) | Transaction amount |
| status | VARCHAR(20) | Transaction status |
| payment_method | VARCHAR(30) | Payment method used |
| | |
Sample Data
| transaction_id | user_id | transaction_time | amount | status | payment_method |
|---|
| 1004 | 102 | 2024-03-02 11:00:00 | 40.00 | completed | card |
| 1001 | 101 | 2024-03-01 09:00:00 | 25.50 | completed | card |
| 1008 | 103 | 2024-03-05 08:00:00 | 15.00 | completed | paypal |
| 1002 | 101 | 2024-03-02 08:30:00 | 10.00 | completed | paypal |
| 1011 | 104 | 2024-03-07 09:00:00 | 12.00 | failed | card |
| 1012 | 104 | 2024-03-07 10:00:00 | 18.00 | completed | card |
| 1015 | 106 | 2024-03-10 10:00:00 | 50.00 | completed | apple_pay |
| 1016 | 106 | 2024-03-11 10:00:00 | 60.00 | completed | apple_pay |
| 1019 | 108 | 2024-03-12 09:00:00 | 22.00 | completed | card |
| 1020 | 108 | 2024-03-12 20:00:00 | 18.00 | completed | null |
| | | | | |
Expected Output
| user_id | first_transaction_time | second_transaction_time | hours_between |
|---|
| 102 | 2024-03-02 11:00:00 | 2024-03-03 09:00:00 | 22.00 |
| 104 | 2024-03-07 10:00:00 | 2024-03-08 08:00:00 | 22.00 |
| 106 | 2024-03-10 10:00:00 | 2024-03-11 10:00:00 | 24.00 |
| 108 | 2024-03-12 09:00:00 | 2024-03-12 20:00:00 | 11.00 |
