Your question is Flag Suspicious Lyft Trip Cards. Start with the requirements and the three tables on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
You are given Lyft trip transaction data and need to identify payment cards showing patterns that may indicate fraud. Write a SQL query that returns cards with suspicious activity in the last 7 days based on trip behavior and payment outcomes. A card should be flagged if it has at least 3 declined attempts, or if it has trips in at least 3 distinct cities, or if its total approved spend exceeds 500 in that 7-day window. Return the card, rider, counts of approved and declined trips, distinct city count, approved spend, and a fraud flag reason.
| Column | Type | Description |
|---|---|---|
| transaction_idPK | INT | Unique transaction identifier |
| trip_id | INT | Trip associated with the payment event |
| rider_id | INT | Rider who initiated the trip |
| card_id | INT | Payment card used for the trip |
| transaction_ts | TIMESTAMP | Transaction timestamp |
| amount_usd | NUMERIC(10,2) | Transaction amount in USD |
| payment_status | VARCHAR(20) | Payment outcome such as approved, declined, or pending |
| Column | Type | Description |
|---|---|---|
| rider_idPK | INT | Unique rider identifier |
| rider_name | VARCHAR(100) | Rider full name |
| home_city | VARCHAR(50) | Rider home city |
| Column | Type | Description |
|---|---|---|
| trip_idPK | INT | Unique trip identifier |
| city | VARCHAR(50) | City where the trip occurred |
| ride_type | VARCHAR(30) | Lyft ride type |
| requested_at | TIMESTAMP | Trip request timestamp |