Problem
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 ashours_between. - Order the final result by
user_id.
Schema
marketplace_transactions
| Column | Type | Description |
|---|---|---|
| transaction_idPK | INT | Unique transaction identifier |
| user_id | INT | Meta user identifier |
| transaction_time | TIMESTAMP | Timestamp when the transaction occurred |
| amount | DECIMAL(10,2) | Transaction amount |
| status | VARCHAR(20) | Transaction status such as completed, failed, pending, or refunded |
| payment_method | VARCHAR(30) | Payment method used for the transaction |
Practicing as: Data Analyst interview at MetaHi, I'll play your Meta interviewer for the Data Analyst role. Candidates describe these interviews as mostly positive and moderately difficult, so expect me to be friendly and conversational. Take your time with the question above and answer like we're in the room.
You are practicing as a guest. Sign up free to run your code against the sample data. Your draft stays right here.
