You are given the task of identifying potentially fraudulent Lyft driver accounts based on signals in the first 7 days after signup. Write a PostgreSQL query that returns drivers who look suspicious because they share a payout bank account with another driver, have multiple failed background checks, or generate unusually high ride cancellation activity shortly after onboarding. Return one row per driver with the driver ID, signup date, count of distinct drivers sharing the same bank account, failed background check count, cancellation rate in the first 7 days, and a final fraud flag.
Use only drivers who signed up in January 2024. A driver should be flagged when they meet at least two of these conditions: shared bank account with at least 2 drivers total, at least 2 failed background checks, or cancellation rate above 40% in the first 7 days.
Output details
- Return columns in this order:
driver_id, signup_date, shared_driver_count, failed_check_count, cancel_rate_7d, fraud_flag.
- Order results by
cancel_rate_7d descending, then failed_check_count descending, then driver_id ascending.
- Round
cancel_rate_7d to two decimal places.
- Treat each seven-day window as signup date inclusive and signup date plus seven days exclusive.
- Treat zero rides as a cancellation rate of
0, and missing failed-check counts as 0.
- Do not count drivers sharing a
NULL bank account.
