SQL and Excel Anomaly Detection

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.

Output

1. One row per anomalous record, with record_id, anomaly_score, anomaly_types, route_id, vehicle_id, and boarded_at.
2. Include only records with at least one anomaly, ordered by descending score, then boarded_at ascending and record_id ascending.

Output details

• Treat a duration as invalid when it is NULL or less than or equal to 0, and a fare as invalid when it is NULL or negative.
• Flag duration outliers only when a route expectation exists, using strict bounds below half or above twice expected_duration_minutes.
• Flag fare outliers only when fare_amount is strictly greater than max_fare, and flag missing references when expected_duration_minutes is NULL.
• Define duplicate trips by matching vehicle_id, boarded_at, origin_station, destination_station, and card_id; flag every matching record.
• Compare timestamps within each vehicle ordered by trip_sequence, then record_id; flag only timestamps strictly earlier than the preceding timestamp.
• Count each applicable anomaly once, and list types in this order: invalid_duration, invalid_fare, duration_outlier, fare_outlier, missing_route_reference, duplicate_trip, out_of_order_timestamp, separated by , .