Task
Lyft Operations wants a simple SQL-based version of an Excel outlier flag. Using a single table of daily station metrics, write a query to identify station-days with unusually high rider cancellation rates.
Requirements
- Return each
station_name, metric_date, and its cancellation rate as cancel_rate_pct.
- Add an
outlier_flag column that is 'Outlier' when cancel_rate_pct is greater than or equal to 15%, otherwise 'Normal'.
- Only include rows where
completed_rides is at least 50.
- Order the results by
cancel_rate_pct descending, then station_name ascending.
Table Definition
lyft_station_daily_metrics
| column | type | description |
|---|
| metric_id | INT | Primary key for the daily metric row |
| station_name | VARCHAR(100) | Lyft pickup station or operating zone name |
| metric_date | DATE | Date of the operational metric |
| completed_rides | INT | Number of completed rides that day |
| cancelled_rides | INT | Number of cancelled rides that day |
| region | VARCHAR(50) | City or operating region |
Sample Data
| metric_id | station_name | metric_date | completed_rides | cancelled_rides | region |
|---|
| 1 | SFO Terminal 2 | 2024-06-03 | 120 | 30 | Bay Area |
| 2 | Mission District | 2024-06-03 | 80 | 8 | Bay Area |
| 3 | LAX-it | 2024-06-03 | 200 | 20 | Los Angeles |
| 4 | DTLA | 2024-06-03 | 55 | 12 | Los Angeles |
| 5 | Wrigleyville | 2024-06-03 | 49 | 20 | Chicago |
| 6 | Navy Pier | 2024-06-03 | 90 | 0 | Chicago |
| 7 | Capitol Hill | 2024-06-03 | 75 | 15 | Seattle |
| 8 | Ballard | 2024-06-03 | 60 | NULL | Seattle |
Expected Output
| station_name | metric_date | cancel_rate_pct | outlier_flag |
|---|
| SFO Terminal 2 | 2024-06-03 | 25.00 | Outlier |
| DTLA | 2024-06-03 | 21.82 | Outlier |
| Capitol Hill | 2024-06-03 | 20.00 | Outlier |
| LAX-it | 2024-06-03 | 10.00 | Normal |
| Mission District | 2024-06-03 | 10.00 | Normal |
| Navy Pier | 2024-06-03 | 0.00 | Normal |
| Ballard | 2024-06-03 | 0.00 | Normal |
