Welcome to the SQL screen.
The question is on your right: Flag Suspicious Lyft Trip Cards. Read through the requirements and the three tables first.
Run and submit your code as often as you need. You also have five interviewer messages this session - want to talk through your approach, or are you ready to start coding?
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 |