How would you use SQL or advanced Excel to identify anomalies in a dataset containing millions of transit records?
Using the provided transit records and route reference data, identify records with invalid values, unusually long or short durations, fares above the route maximum, duplicate trip signatures, missing route references, or timestamps earlier than the preceding vehicle sequence.
record_id, anomaly_score, anomaly_types, route_id, vehicle_id, and boarded_at.boarded_at ascending and record_id ascending.| Column | Type | Description |
|---|---|---|
| record_idPK | INT | Unique transit record identifier |
| vehicle_id | VARCHAR(20) | Vehicle identifier |
| route_id | VARCHAR(20) | Recorded route identifier |
| trip_sequence | INT | Sequence number for the vehicle |
| boarded_at | TIMESTAMP | Boarding timestamp |
| card_id | VARCHAR(30) | Transit card identifier |
| origin_station | VARCHAR(50) | Origin station |
| destination_station | VARCHAR(50) | Destination station |
| duration_minutes | DECIMAL(8,2) | Recorded trip duration |
| fare_amount | DECIMAL(8,2) | Charged fare amount |
| Column | Type | Description |
|---|---|---|
| route_idPK | VARCHAR(20) | Reference route identifier |
| expected_duration_minutes | DECIMAL(8,2) | Expected route duration |
| max_fare | DECIMAL(8,2) | Maximum valid fare for the route |