Analyze NYC taxi trips efficiently over last 7 days
Company: Two Sigma
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Online Assessment
Use today = 2025-09-01. Consider NYC taxi trip data over the last 7 days inclusive (2025-08-26 to 2025-09-01, America/New_York). You receive two datasets and must write a fast, vectorized analysis (no Python for-loops over rows). Data schema and tiny samples:
trips(id, taxi_id, pickup_ts, dropoff_ts, pickup_zone_id, dropoff_zone_id, distance_miles, fare_amount)
1 | 101 | 2025-08-26 08:15 | 2025-08-26 08:45 | 1 | 3 | 6.0 | 18.50
2 | 102 | 2025-08-26 00:20 | 2025-08-26 00:50 | 2 | 1 | 4.0 | 14.00
3 | 101 | 2025-08-28 01:10 | 2025-08-28 01:40 | 1 | 1 | 3.0 | 12.00
4 | 103 | 2025-08-30 17:05 | 2025-08-30 17:25 | 4 | 2 | 2.5 | 9.50
5 | 104 | 2025-09-01 02:30 | 2025-09-01 03:20 | 1 | 4 | 10.0 | 30.00
6 | 102 | 2025-08-31 23:50 | 2025-09-01 00:10 | 3 | 3 | 5.0 | 16.00
zones(zone_id, borough)
1 | Manhattan
2 | Brooklyn
3 | Queens
4 | Bronx
Tasks:
1) After joining trips with zones on pickup_zone_id, compute per (borough, hour_of_day from pickup_ts) the median trip speed in mph, where speed = distance_miles / duration_hours. Filter trips to 1 ≤ duration_minutes ≤ 120 and 1 ≤ speed ≤ 80. Return the top 3 (borough, hour) pairs by median speed; break ties by borough asc, then hour asc. Report the exact (borough, hour, median_speed_mph) triples. 2) For trips with pickup borough = 'Manhattan' and pickup time between 00:00 and 05:00 inclusive, identify the 3 taxi_id with the largest 95th percentile of trip duration (minutes) over the same date range; break ties by taxi_id asc. Clearly define how you compute the 95th percentile (e.g., pandas/numpy method) and use a stable, vectorized approach. 3) Provide pandas code (or SQL) that runs in O(n log n) or better due to grouping/quantile operations, avoids per-row loops, and uses: parsed datetime dtypes; one-to-many join performed once; categorical dtype for borough; appropriate indexing on pickup_ts for time filtering. 4) Briefly justify two memory/performance optimizations you employ (e.g., downcasting floats/ints, using groupby-agg with quantile in a single pass, avoiding intermediate copies).
Overview: This question evaluates proficiency in time-series filtering, relational joins, grouped aggregations (median and high-percentile calculations), numeric stability of percentile methods, and performance-aware vectorized data manipulation using SQL or pandas.
Top 3 (borough, hour) by median NYC taxi trip speed (last 7 days)
You are given NYC taxi trips and a zone-to-borough mapping. Consider trips with pickup_ts in the inclusive date range 2025-08-26 to 2025-09-01 (filter as pickup_ts >= '2025-08-26 00:00:00' and pickup_ts < '2025-09-02 00:00:00').
Join trips to zones on trips.pickup_zone_id = zones.zone_id. For each (borough, hour_of_day) where hour_of_day is extracted from pickup_ts (0-23), compute the median trip speed in mph, where:
- duration_minutes = (dropoff_ts - pickup_ts) in minutes
- duration_hours = duration_minutes / 60
- speed_mph = distance_miles / duration_hours
Filter to trips with 1 <= duration_minutes <= 120 and 1 <= speed_mph <= 80.
Return the top 3 (borough, hour_of_day) pairs by median speed (descending). Break ties by borough ascending, then hour_of_day ascending.
Output columns: borough, hour_of_day, median_speed_mph.
Tables
zones(zone_id INT, borough VARCHAR(50))
trips(id INT, taxi_id INT, pickup_ts TIMESTAMP, dropoff_ts TIMESTAMP, pickup_zone_id INT, dropoff_zone_id INT, distance_miles DECIMAL(6,2), fare_amount DECIMAL(8,2))
Hints
- Compute duration using EXTRACT(EPOCH FROM (dropoff_ts - pickup_ts)) and derive speed from it.
- Use PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY speed_mph) to get the median per group.
Top 3 Manhattan late-night taxis by 95th percentile trip duration
Using the same tables, consider trips with pickup_ts in the inclusive date range 2025-08-26 to 2025-09-01 (filter as pickup_ts >= '2025-08-26 00:00:00' and pickup_ts < '2025-09-02 00:00:00').
Filter to trips where the pickup borough is 'Manhattan' (join on pickup_zone_id) AND the pickup time-of-day is between 00:00:00 and 05:00:00 inclusive.
For each taxi_id, compute the 95th percentile of trip duration in minutes using a continuous percentile definition (i.e., SQL PERCENTILE_CONT(0.95)).
Return the 3 taxi_id with the largest 95th percentile duration. Break ties by taxi_id ascending.
Output columns: taxi_id, p95_duration_minutes.
Tables
zones(zone_id INT, borough VARCHAR(50))
trips(id INT, taxi_id INT, pickup_ts TIMESTAMP, dropoff_ts TIMESTAMP, pickup_zone_id INT, dropoff_zone_id INT, distance_miles DECIMAL(6,2), fare_amount DECIMAL(8,2))
Hints
- Use pickup_ts::time (or an equivalent time extraction function) to filter the 00:00:00–05:00:00 window.
- Use PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY duration_minutes) for a continuous percentile.