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 |