Task
You are given driver activity, support contact, and market-level supply data from a Lyft market. Write a SQL query to identify which driver segments have the highest churn rate in the most recent completed month. Treat a driver as churned if they completed at least one ride in the prior month but completed zero rides in the most recent month. Return churn rate by market, tenure bucket, support-contact bucket, and utilization bucket so you can compare likely drivers of churn.
Use the latest month in the dataset as the current month and the month immediately before it as the baseline month. Include only drivers who were active in the baseline month. Order the output by churn rate descending, then by baseline driver count descending.
Schema
driver_monthly_activity
| column | type | description |
|---|
| driver_id | INT | Driver identifier |
| market_id | INT | Lyft market identifier |
| activity_month | DATE | First day of the month |
| rides_completed | INT | Completed rides in that month |
| hours_online | NUMERIC(10,2) | Online hours in that month |
| earnings_usd | NUMERIC(10,2) | Driver earnings in USD |
| | |
driver_support_contacts
| column | type | description |
|---|
| contact_id | INT | Support contact identifier |
| driver_id | INT | Driver identifier |
| contact_date | DATE | Date of support contact |
| contact_reason | VARCHAR(50) | Support contact reason |
| | |
market_monthly_supply
| column | type | description |
|---|
| market_id | INT | Lyft market identifier |
| supply_month | DATE | First day of the month |
| avg_eta_minutes | NUMERIC(5,2) | Average ETA in market |
| bonus_hours | NUMERIC(10,2) | Incentive hours offered |
| | |
Sample data
driver_monthly_activity
| driver_id | market_id | activity_month | rides_completed | hours_online | earnings_usd |
|---|
| 103 | 1 | 2024-02-01 | 18 | 12.00 | 210.00 |
| 101 | 1 | 2024-03-01 | 0 | 0.00 | 0.00 |
| 108 | 2 | 2024-03-01 | 0 | 0.00 | 0.00 |
driver_support_contacts
| contact_id | driver_id | contact_date | contact_reason |
|---|
| 1 | 101 | 2024-02-10 | payout_issue |
| 5 | 104 | 2024-02-18 | app_bug |
| 9 | 109 | 2024-03-03 | safety |
market_monthly_supply
| market_id | supply_month | avg_eta_minutes | bonus_hours |
|---|
| 1 | 2024-02-01 | 4.80 | 120.00 |
| 2 | 2024-02-01 | 7.20 | 80.00 |
| 3 | 2024-02-01 | 5.50 | 60.00 |
Expected output
| market_id | tenure_bucket | support_bucket | utilization_bucket | baseline_drivers | churned_drivers | churn_rate |
|---|
| 1 | new | 2+ contacts | low_utilization | 1 | 1 | 1.0000 |
| 2 | new | 1 contact | low_utilization | 1 | 1 | 1.0000 |
| 3 | new | 0 contacts | low_utilization | 1 | 1 | 1.0000 |
| 1 | tenured | 0 contacts | low_utilization | 1 | 1 | 1.0000 |
| 1 | tenured | 1 contact | high_utilization | 1 | 0 | 0.0000 |
| 2 | tenured | 0 contacts | high_utilization | 2 | 0 | 0.0000 |
