Coding questions · Two Sigma
Analyze NYC taxi trips efficiently over last 7 days
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.
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:
- 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).
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.
Table zones
| column | type | constraints |
|---|---|---|
| zone_id | INT | PRIMARY KEY, NOT NULL |
| borough | VARCHAR(50) | NOT NULL |
Sample data:
| borough | zone_id |
|---|---|
| Manhattan | 1 |
| Brooklyn | 2 |
| Queens | 3 |
| Bronx | 4 |
Table trips
| column | type | constraints |
|---|---|---|
| id | INT | PRIMARY KEY, NOT NULL |
| taxi_id | INT | NOT NULL |
| pickup_ts | TIMESTAMP | NOT NULL |
| dropoff_ts | TIMESTAMP | NOT NULL |
| pickup_zone_id | INT | NOT NULL |
| dropoff_zone_id | INT | NOT NULL |
| distance_miles | DECIMAL(6,2) | NOT NULL |
| fare_amount | DECIMAL(8,2) | NOT NULL |
Sample data:
| distance_miles | dropoff_ts | dropoff_zone_id | fare_amount | id | pickup_ts | pickup_zone_id | taxi_id |
|---|---|---|---|---|---|---|---|
| 6 | 2025-08-26 08:45:00 | 3 | 18.5 | 1 | 2025-08-26 08:15:00 | 1 | 101 |
| 4 | 2025-08-26 00:50:00 | 1 | 14 | 2 | 2025-08-26 00:20:00 | 2 | 102 |
| 3 | 2025-08-28 01:40:00 | 1 | 12 | 3 | 2025-08-28 01:10:00 | 1 | 101 |
| 2.5 | 2025-08-30 17:25:00 | 2 | 9.5 | 4 | 2025-08-30 17:05:00 | 4 | 103 |
| 10 | 2025-09-01 03:20:00 | 4 | 30 | 5 | 2025-09-01 02:30:00 | 1 | 104 |
| 5 | 2025-09-01 00:10:00 | 3 | 16 | 6 | 2025-08-31 23:50:00 | 3 | 102 |
| 8 | 2025-08-27 05:40:00 | 2 | 25 | 7 | 2025-08-27 04:50:00 | 1 | 105 |
| 9 | 2025-08-29 00:55:00 | 3 | 26 | 8 | 2025-08-29 00:05:00 | 1 | 101 |
| 4.5 | 2025-08-31 05:30:00 | 2 | 13.5 | 9 | 2025-08-31 05:00:00 | 1 | 104 |
| 12 | 2025-08-30 04:30:00 | 4 | 35 | 10 | 2025-08-30 03:00:00 | 1 | 106 |
Expected output
Top 3 borough/hour pairs ranked by median speed (mph) after filtering by duration and speed constraints.
| borough | hour_of_day | median_speed_mph |
|---|---|---|
| Queens | 23 | 15 |
| Manhattan | 2 | 12 |
| Manhattan | 8 | 12 |
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.
- Filter the pickup_ts range as [2025-08-26, 2025-09-02) to include all of 2025-09-01.
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.
Table zones
| column | type | constraints |
|---|---|---|
| zone_id | INT | PRIMARY KEY, NOT NULL |
| borough | VARCHAR(50) | NOT NULL |
Sample data:
| borough | zone_id |
|---|---|
| Manhattan | 1 |
| Brooklyn | 2 |
| Queens | 3 |
| Bronx | 4 |
Table trips
| column | type | constraints |
|---|---|---|
| id | INT | PRIMARY KEY, NOT NULL |
| taxi_id | INT | NOT NULL |
| pickup_ts | TIMESTAMP | NOT NULL |
| dropoff_ts | TIMESTAMP | NOT NULL |
| pickup_zone_id | INT | NOT NULL |
| dropoff_zone_id | INT | NOT NULL |
| distance_miles | DECIMAL(6,2) | NOT NULL |
| fare_amount | DECIMAL(8,2) | NOT NULL |
Sample data:
| distance_miles | dropoff_ts | dropoff_zone_id | fare_amount | id | pickup_ts | pickup_zone_id | taxi_id |
|---|---|---|---|---|---|---|---|
| 6 | 2025-08-26 08:45:00 | 3 | 18.5 | 1 | 2025-08-26 08:15:00 | 1 | 101 |
| 4 | 2025-08-26 00:50:00 | 1 | 14 | 2 | 2025-08-26 00:20:00 | 2 | 102 |
| 3 | 2025-08-28 01:40:00 | 1 | 12 | 3 | 2025-08-28 01:10:00 | 1 | 101 |
| 2.5 | 2025-08-30 17:25:00 | 2 | 9.5 | 4 | 2025-08-30 17:05:00 | 4 | 103 |
| 10 | 2025-09-01 03:20:00 | 4 | 30 | 5 | 2025-09-01 02:30:00 | 1 | 104 |
| 5 | 2025-09-01 00:10:00 | 3 | 16 | 6 | 2025-08-31 23:50:00 | 3 | 102 |
| 8 | 2025-08-27 05:40:00 | 2 | 25 | 7 | 2025-08-27 04:50:00 | 1 | 105 |
| 9 | 2025-08-29 00:55:00 | 3 | 26 | 8 | 2025-08-29 00:05:00 | 1 | 101 |
| 4.5 | 2025-08-31 05:30:00 | 2 | 13.5 | 9 | 2025-08-31 05:00:00 | 1 | 104 |
| 12 | 2025-08-30 04:30:00 | 4 | 35 | 10 | 2025-08-30 03:00:00 | 1 | 106 |
Expected output
Top 3 taxi_id values ranked by the continuous 95th percentile of trip duration (minutes) for Manhattan pickups between 00:00 and 05:00 inclusive.
| p95_duration_minutes | taxi_id |
|---|---|
| 90 | 106 |
| 50 | 105 |
| 49 | 101 |
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.
- Apply the date filter before grouping to keep the computation efficient.