runtimequestion 369 Profiling & Optimization · hard
Fare totals per borough, from a slow join to an index
Each taxi trip has a pickup zone ID; the zone table maps zone IDs to boroughs. Write borough_totals(trips, zones) that adds up fares per borough. A month has millions of trips and the zone table 265 rows, so look zones up through an index built once, not by scanning the table per trip. Trips whose zone is missing from the table count under "Unknown".
Function
borough_totals(trips, zones)tripsa list of
{"pickup_zone_id": int, "fare": float}zonesa list of
{"zone_id": int, "borough": str}Returnsa list of
(borough, total) tuples, highest total first, ties by borough nameConstraintthe zone table is read about once: at most 600 reads for 300 zones and 400 trips
Examples
callborough_totals([{'pickup_zone_id': 186, 'fare': 40.8}, {'pickup_zone_id': 132, 'fare': 70}, {'pickup_zone_id': 48, 'fare': 7.2}], [{'zone_id': 132, 'borough': 'Queens'}, {'zone_id': 186, 'borough': 'Manhattan'}, {'zone_id': 48, 'borough': 'Manhattan'}])returns[('Queens', 70.0), ('Manhattan', 48.0)]
callborough_totals([], [{'zone_id': 1, 'borough': 'EWR'}])returns[]
callborough_totals([{'pickup_zone_id': 999, 'fare': 12.5}], [{'zone_id': 1, 'borough': 'EWR'}])returns[('Unknown', 12.5)]