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.
station_name, metric_date, and its cancellation rate as cancel_rate_pct.outlier_flag column that is 'Outlier' when cancel_rate_pct is greater than or equal to 15%, otherwise 'Normal'.completed_rides is at least 50.cancel_rate_pct descending, then station_name ascending.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 |
| 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 |
| 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 |
| Column | Type | Description |
|---|---|---|
| metric_idPK | 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 |
| 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 | Seattle | |
| 9 | Brooklyn Heights | 2024-06-03 | 50 | 5 | New York |
| 10 | Williamsburg | 2024-06-03 | 140 | 21 | New York |
| 11 | Hollywood | 2024-06-03 | 143 | 17 | Bronx |
| 12 | Wrigleyville | 2024-06-03 | 192 | 5 | Long Beach |
| 13 | Venice | 2024-06-01 | 165 | null | Tacoma |
| 14 | Queen Anne | 2024-06-01 | 131 | 15 | Burbank |
| 15 | Venice | 2024-06-06 | 62 | 3 | Los Angeles |
| 16 | South Lake Union | 2024-05-31 | 176 | 20 | New York |
| 17 | Lincoln Park | 2024-06-01 | 123 | 27 | Queens |
| 18 | Venice | 2024-06-02 | 153 | -3 | null |
| 19 | Fremont | 2024-05-31 | 190 | 25 | null |
| 20 | South Lake Union | 2024-06-02 | 28 | 3 | Manhattan |
| 21 | Hollywood | 2024-06-03 | 65 | 33 | Santa Monica |
| 22 | Navy Pier | 2024-06-04 | 203 | 13 | Redwood City |
| 23 | Capitol Hill | 2024-06-03 | 174 | 9 | null |
| 24 | Brooklyn Heights | 2024-05-31 | 97 | 7 | San Francisco |
| 25 | South Lake Union | 2024-06-02 | 156 | null | Long Beach |
| station_name | metric_date | cancel_rate_pct | outlier_flag |
|---|---|---|---|
| Hollywood | 2024-06-03 | 50.77 | Outlier |
| SFO Terminal 2 | 2024-06-03 | 25.00 | Outlier |
| Lincoln Park | 2024-06-01 | 21.95 | Outlier |
| DTLA | 2024-06-03 | 21.82 | Outlier |
| Capitol Hill | 2024-06-03 | 20.00 | Outlier |
| Williamsburg | 2024-06-03 | 15.00 | Outlier |
| Fremont | 2024-05-31 | 13.16 | Normal |
| Hollywood | 2024-06-03 | 11.89 | Normal |
| Queen Anne | 2024-06-01 | 11.45 | Normal |
| South Lake Union | 2024-05-31 | 11.36 | Normal |
| Brooklyn Heights | 2024-06-03 | 10.00 | Normal |
| LAX-it | 2024-06-03 | 10.00 | Normal |
| Mission District | 2024-06-03 | 10.00 | Normal |
| Brooklyn Heights | 2024-05-31 | 7.22 | Normal |
| Navy Pier | 2024-06-04 | 6.40 | Normal |
| Capitol Hill | 2024-06-03 | 5.17 | Normal |
| Venice | 2024-06-06 | 4.84 | Normal |
| Wrigleyville | 2024-06-03 | 2.60 | Normal |
| Ballard | 2024-06-03 | 0.00 | Normal |
| Navy Pier | 2024-06-03 | 0.00 | Normal |
| South Lake Union | 2024-06-02 | 0.00 | Normal |
| Venice | 2024-06-01 | 0.00 | Normal |
| Venice | 2024-06-02 | -1.96 | Normal |
