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.
Output details
- Treat
DATE '2024-05-15' as today's date.
- Use the window from
2024-05-08 00:00:00 inclusive through 2024-05-15 00:00:00 exclusive.
- Return columns in this order:
card_id, rider_name, approved_trip_count, declined_trip_count, distinct_city_count, approved_spend_usd, fraud_flag_reason.
- Sort by
approved_spend_usd descending, then declined_trip_count descending, then card_id ascending.
- Use exact fraud reasons:
declined_attempts, multi_city_activity, high_approved_spend, and their specified combined labels, prioritizing all three conditions first.
- Treat approved spend with no qualifying approved amount as
0.
