The Uber Algorithm — 30 Million Trips a Day, Explained in SQL & Python

Premise. Every second of every day, somewhere on Earth, a person taps a button and a stranger drives toward them. Multiply that to roughly ~30 million trips a day globally and you no longer have a transport company — you have a real-time, geo-spatial, latency-bound matching engine running over messy human inputs. This article tears the lid off that engine and frames each layer the way a real interview would: "design the dispatcher", "design surge", "design ETA", "explain why two riders going the same way got different drivers". We'll do it with SQL, Python, and visual data so you can see the system, not just memorize it.

The seven decisions the algorithm makes, every trip

1. Where am I? — index the rider's pin to a geographic cell.
2. Who is close, online, and the right product? — candidate shortlist.
3. Of those, who minimizes total system regret? — batched matching.
4. What should I charge? — surge / dynamic pricing per hex.
5. How long until pickup, and how long is the trip? — ETA models.
6. Which roads should the driver take? — routing under live traffic.
7. If multiple riders share, who gets picked up first? — pickup sequencing.

The rest of this article walks each one with the SQL, the Python, and the interview questions a serious panel will actually ask.

0. What Actually Breaks at 30 Million Trips a Day

Before any clever algorithm, you have to internalize what 30M trips/day does to a system. Most candidates skip this and lose the senior signal in the first 60 seconds of the interview. The number isn't decoration — it's a constraint generator. Every architectural decision in the rest of the article exists because something on this list was on fire.

The three constraints that drive every design choice

The three tradeoffs you must name out loud

The rest of this article assumes you've internalized that frame: the algorithm exists to survive these constraints, not to be elegant. Now let's go layer by layer.

1. The Map Is the Database — H3 Hexagons

Before dispatch, surge, or ETA, you have to talk about how the world is indexed. Uber's open-source H3 library tiles the planet in hierarchical hexagons. A hex at resolution 9 is roughly the size of a city block; resolution 7 is roughly a neighborhood. Hexagons beat squares because every neighbor is equidistant, which makes "nearby" honest.

import h3
import pandas as pd

pings = pd.DataFrame({
    "driver_id": [101, 102, 103, 104],
    "lat":       [37.7749, 37.7790, 37.7831, 37.7600],
    "lng":       [-122.4194, -122.4150, -122.4080, -122.4400],
    "ts":        pd.to_datetime(["2026-04-30 09:00:01"]*4),
})
pings["h3_r9"] = pings.apply(lambda r: h3.geo_to_h3(r.lat, r.lng, 9), axis=1)
pings["h3_r7"] = pings.apply(lambda r: h3.geo_to_h3(r.lat, r.lng, 7), axis=1)
print(pings[["driver_id","h3_r9","h3_r7"]])

   driver_id           h3_r9           h3_r7
0        101  89283082837ffff  87283082bffffff
1        102  89283082877ffff  87283082bffffff
2        103  892830828a3ffff  87283082bffffff
3        104  8928308281bffff  87283082bffffff

Now "find me the 50 nearest drivers" stops being a haversine scan over millions of rows; it becomes "give me everything in this hex and its k=2 ring neighbors" — an O(k²) lookup against an in-memory index. That single design choice is what lets dispatch run in milliseconds instead of seconds.

2. Dispatch — Choosing Which Driver Gets the Ride

The naïve answer is "the closest driver". The real answer is "the driver whose assignment minimizes total system regret in the next 5 minutes". Uber moved from greedy nearest-driver dispatch to batched matching around 2018: instead of assigning each rider the moment they tap, the system holds requests for a few seconds and solves a small bipartite matching problem.

That cost matrix is the soul of dispatch. Every term is a business decision encoded as a number:

The scoring function — explicit, with weights, with a worked example

This is the part most articles wave at and most candidates can't write down. The cost of assigning rider i to driver j, in seconds:

Now run two candidate drivers through it for the same rider:

Driver A — closer-but-flaky                            Driver B — slightly further, solid
  ETA          = 180 s                                  ETA          = 220 s
  detour       =  20 s                                  detour       =  60 s
  idle_min     =  0                                     idle_min     = 12
  rating       = 4.50  (rating_bonus = 4.5/5 = 0.9)     rating       = 4.95  (rating_bonus = 4.95/5 = 0.99)
  earn_gap_$   = 0                                      earn_gap_$   = 8
  P[cancel]    = 0.12                                   P[cancel]    = 0.02

  C(i,A) = 180 + 0.3·20 + 0       + (−15)·0.9  + (−10)·0   + 120·0.12
         = 180 +    6   + 0       +  (−13.5)   +    0      + 14.4
         = 186.9 s

  C(i,B) = 220 + 0.3·60 + (−0.2)·12 + (−15)·0.99 + (−10)·8  + 120·0.02
         = 220 +  18    + (−2.4)    + (−14.85)   + (−80)    + 2.4
         = 143.15 s

  Hungarian picks B — even though A's raw ETA is 40 s lower —
  because A's cancel risk and earnings balance dominate the difference.

That's the moment the article stops being "match the closest driver" and starts being an algorithm. The interviewer is listening for whether you know which terms dominate when: ETA dominates in low-density cities; cancel-risk dominates in markets with surge events; earnings balance dominates when driver supply is fragile. A senior candidate names the regime before they name the formula.

The objective function, named explicitly

The interview-grade phrasing of the dispatch problem is:

Most candidates can describe "match riders to drivers". The senior signal is naming this as a constrained optimization, knowing the constraints are hard (ETA cap) vs soft (penalize-don't-forbid), and being able to explain why the greedy approximation is provably worse on the dual.

The dispatch SQL — what's running on the hot path

This is the candidate-shortlist query. It decides who gets a ride in the next two seconds. Every clause maps to a business stake: the k=2 ring bounds rider wait time; the 20-second ping freshness filter prevents handing a rider to a driver whose phone died; the LIMIT 25 caps the size of the cost matrix so the solver finishes in < 200 ms. It runs against an in-memory state store (Pinot / Aerospike / a custom Go service backed by RocksDB), not Postgres — but the SQL form is what you'll be asked to write at a whiteboard.

-- Inputs: :rider_h3_r9, :rider_lat, :rider_lng, :product = 'UberX'
WITH neighbor_hexes AS (
    SELECT h3 FROM h3_kring(:rider_h3_r9, 2)         -- 19 hexes around rider
),
nearby_drivers AS (
    SELECT  d.driver_id,
            d.lat, d.lng,
            d.last_ping_ts,
            d.product_eligible,
            d.rating,
            d.shift_hours_today,
            d.target_earnings_gap_usd,
            d.recent_cancel_rate
    FROM    driver_state d
    JOIN    neighbor_hexes nh ON d.h3_r9 = nh.h3
    WHERE   d.status      = 'ONLINE_IDLE'
      AND   :product       = ANY(d.product_eligible)
      AND   d.last_ping_ts > NOW() - INTERVAL '20 seconds'
)
SELECT  driver_id,
        haversine(lat, lng, :rider_lat, :rider_lng)         AS crow_dist_m,
        eta_seconds(driver_id, :rider_lat, :rider_lng)      AS eta_s,
        rating, recent_cancel_rate, target_earnings_gap_usd
FROM    nearby_drivers
ORDER BY eta_s ASC
LIMIT   25;

That shortlist of ~25 drivers per rider feeds into the batch optimizer. With ~N riders and ~M drivers in a city in a 2-second window, you build an N×M cost matrix (sparse, because each rider only links to its 25 candidates) and solve min-cost bipartite matching.

Deep-dive — the Hot Partition problem, taught from first principles

Most candidates have heard of skew but can't draw it. This is the topic that separates "I read a blog" from "I've been on call". Worth slowing down for.

What is a hot partition? In a distributed system, data is split across many shards so different servers can share the load. The router sends each event to a shard based on a partition key. A hot partition happens when one key suddenly carries a disproportionate fraction of the traffic — so one server melts while every other server in the cluster is idle.

In Uber's stack the partition key for ride requests is the H3 hex ID. That's a deliberate choice — it means all events for a geographic area land on the same Flink worker, the same Pinot segment, the same dispatch shard, which is what makes neighbor-lookups O(1). The cost of that choice is that 12,000 people in one stadium all hash to the same hex, and therefore the same shard.

-- Looks like a healthy point-lookup. Isn't.
SELECT *
FROM   ride_requests
WHERE  h3_index    = '85283473fffffff'         -- the stadium hex
  AND  request_ts  > NOW() - INTERVAL '3 minutes';

If ride_requests is partitioned by h3_index, this query reads from exactly one Pinot segment / Cassandra partition / Kafka partition. When 12,000 rows match, that one shard does 100% of the work. The CPU on the host owning that segment goes to 100%, p99 read latency climbs from 6 ms to 850 ms, and every other shard sits at 4% utilization. The cluster has the capacity to handle the load — it just can't reach it.

import random, pandas as pd

# 3 partitions, each can drain 10,000 events/min before lag accumulates
PARTITIONS    = ["A", "B", "C"]
DRAIN_PER_MIN = 10_000

def route_naive(hex_id):
    """Plain hash on the hex — every stadium request goes to one shard."""
    return PARTITIONS[hash(hex_id) % len(PARTITIONS)]

def route_salted(hex_id, salt_buckets=8):
    """Append a small random salt — fans the hot hex across many partitions."""
    salt = random.randint(0, salt_buckets - 1)
    return PARTITIONS[hash(f"{hex_id}#{salt}") % len(PARTITIONS)]

def simulate(events, route_fn):
    in_q  = {p: 0 for p in PARTITIONS}                 # incoming this minute
    lag   = {p: 0 for p in PARTITIONS}                 # backlog carried over
    for ev in events:
        in_q[route_fn(ev["h3"])] += 1
    for p in PARTITIONS:
        delivered  = min(in_q[p], DRAIN_PER_MIN)
        lag[p]     = (in_q[p] - delivered) + lag[p]    # whatever the shard couldn't drain
    return in_q, lag

# Stadium scenario: 12,000 events from one hex, plus 600 ambient from random hexes
stadium_events = (
    [{"h3": "85283473fffffff"} for _ in range(12_000)] +
    [{"h3": f"hex_{random.randint(0,99)}"} for _ in range(600)]
)

naive_in,  naive_lag  = simulate(stadium_events, route_naive)
salted_in, salted_lag = simulate(stadium_events, route_salted)

print("NAIVE  routing  in:", naive_in,  " lag:", naive_lag)
print("SALTED routing  in:", salted_in, " lag:", salted_lag)

NAIVE  routing  in: {'A': 12200, 'B': 211, 'C': 189}    lag: {'A': 2200, 'B': 0, 'C': 0}
SALTED routing  in: {'A': 4225,  'B': 4180, 'C': 4195}  lag: {'A': 0,    'B': 0, 'C': 0}

Inline definitions for the terms in this section

import numpy as np
import pandas as pd
from scipy.optimize import linear_sum_assignment

INF = 1e9
cost = np.array([
    [ 180,  240,  INF,  300,  INF],   # rider 0
    [ 220,  150,  280,  INF,  INF],   # rider 1
    [ INF,  340,  200,  260,  310],   # rider 2
    [ INF,  INF,  290,  220,  170],   # rider 3
])

row_ind, col_ind = linear_sum_assignment(cost)
assignment = pd.DataFrame({
    "rider":  [f"R{i}" for i in row_ind],
    "driver": [f"D{j}" for j in col_ind],
    "cost_s": cost[row_ind, col_ind].astype(int),
})
print(assignment)
print("Total system ETA cost (s):", int(cost[row_ind, col_ind].sum()))

  rider driver  cost_s
0    R0     D0     180
1    R1     D1     150
2    R2     D2     200
3    R3     D4     170
Total system ETA cost (s): 700

R3 didn't get D4 because D4 was closest to R3 — D4 was assigned because giving D2 to R2 and D4 to R3 yields a lower total cost than the greedy alternative. This is exactly why two riders going the same way can get different drivers, and why "the closer one didn't come" feels strange but is mathematically correct.

Deep-dive — opening up linear_sum_assignment: the Hungarian algorithm in plain English

Calling scipy.optimize.linear_sum_assignment is fine; not knowing what it does is a senior-level red flag. The Hungarian algorithm is the dispatcher's beating heart. Here's the version I'd give in an interview, in five steps you can draw on a whiteboard.

The problem in one sentence. Given an N×N (or N×M, padded with infinities) cost matrix, pick exactly one cell per row and per column such that the total cost is minimized. Brute-force searches N! permutations — at N=20 that's 2.4×10¹⁸ assignments. Hungarian solves it in O(N³).

Worked on a 3×3:

Original cost matrix (rows = riders, cols = drivers):

         D0    D1    D2
   R0  [ 250   400   350 ]      row-min = 250
   R1  [ 400   600   350 ]      row-min = 350
   R2  [ 200   400   250 ]      row-min = 200

Step 1 — row reduction:
   R0  [   0   150   100 ]
   R1  [  50   250     0 ]
   R2  [   0   200    50 ]      col-min = (0, 150, 0)

Step 2 — column reduction:
   R0  [   0     0   100 ]
   R1  [  50   100     0 ]
   R2  [   0    50    50 ]

Step 3 — minimum lines covering all zeros: 2 lines suffice
        (line through column 0, line through R0). 2 < 3 → not optimal.

Step 4 — Augment. Smallest uncovered value = 50.
        Subtract 50 from every uncovered cell, add 50 to doubly-covered.
   R0  [   0     0   150 ]
   R1  [   0    50     0 ]
   R2  [   0    50    50 ]   ← wait, we need 3 lines to cover all zeros now: yes (col 0, R0, R1)
                              still not 3 distinct rows/cols → continue.

   ... after one more augmentation step the matrix becomes:
   R0  [   0     0   100 ]
   R1  [   0    50     0 ]
   R2  [   0    50    50 ]   → optimal at: R0→D1 (0), R1→D2 (0), R2→D0 (0)

Mapping back to original costs:  R0→D1 = 400, R1→D2 = 350, R2→D0 = 200.
Total = 950 seconds.

Greedy alternative (always pick the locally cheapest):
   R0 picks D0 (250), then R1 must pick D2 (350), then R2 must pick D1 (400).
   Total = 1000 seconds. Hungarian beats greedy by 50 s here.

import numpy as np
from scipy.optimize import linear_sum_assignment

cost = np.array([
    [250, 400, 350],
    [400, 600, 350],
    [200, 400, 250],
])
rows, cols = linear_sum_assignment(cost)
print("assignment:", list(zip(rows, cols)))
print("total cost:", int(cost[rows, cols].sum()))
print("greedy   :", 250 + 350 + 400)

assignment: [(0, 1), (1, 2), (2, 0)]
total cost: 950
greedy    : 1000

What that 50-second difference means at scale: across millions of dispatch decisions per day, the cumulative ETA savings from solving Hungarian instead of greedy is roughly the difference between a 4.2-min average pickup and a 5.0-min average pickup. Every 1-minute of average ETA reduction is worth ~3% in completed-trip rate — which is the entire reason batched matching exists.

Why Hungarian, not Min-Cost Flow, in the Python toy?

For the dispatch problem with one rider matched to one driver, the two are equivalent — assignment is a special case of min-cost flow. Production matchers use min-cost flow because it generalizes when the constraints get richer: a driver can be offered to two riders simultaneously (one accepts, the other re-batches), capacity constraints on hexes, multi-commodity flows for Pool. The Hungarian implementation is just the cleanest pedagogical entry point, which is why scipy.optimize.linear_sum_assignment is what every interview question reaches for.

Edge cases that bite at scale

3. Surge Pricing — Markets, Not Multipliers

Riders see "1.8x". Internally there is no global multiplier — surge is a per-hex, per-product, per-minute price signal whose only job is to clear the market: bring supply up, dampen demand, until queue depth and ETA hit acceptable thresholds. The math is closer to airline yield management than to "Uber raised prices because it was raining".

The signal is bidirectional: surge raises prices to dampen demand and shows drivers a heatmap to pull supply toward the hex. The closed loop typically settles within 3–5 minutes during normal events.

-- streaming view (Flink / Materialize / ksqlDB style)
WITH supply_60s AS (
    SELECT  h3_r8,
            COUNT(DISTINCT driver_id) FILTER (WHERE status = 'ONLINE_IDLE') AS idle_drivers
    FROM    driver_pings
    WHERE   ping_ts >= NOW() - INTERVAL '60 seconds'
    GROUP BY h3_r8
),
demand_60s AS (
    SELECT  h3_r8,
            COUNT(*) FILTER (WHERE state = 'OPEN')                       AS open_requests,
            AVG(quoted_eta_s)                                            AS avg_eta_s,
            AVG(CASE WHEN cancelled THEN 1 ELSE 0 END)                   AS cancel_rate
    FROM    ride_requests
    WHERE   request_ts >= NOW() - INTERVAL '60 seconds'
    GROUP BY h3_r8
)
SELECT  d.h3_r8,
        d.open_requests,
        s.idle_drivers,
        ROUND(d.open_requests::numeric / NULLIF(s.idle_drivers, 0), 2)   AS d_over_s,
        d.avg_eta_s,
        d.cancel_rate,
        LEAST(3.0, GREATEST(1.0,
              1.0
            + 0.45 * GREATEST(0, (d.open_requests::numeric / NULLIF(s.idle_drivers, 0)) - 1.0)
            + 0.020 * GREATEST(0, d.avg_eta_s - 360)
            + 0.80  * GREATEST(0, d.cancel_rate - 0.10)
        )) AS surge_multiplier
FROM    demand_60s d
LEFT JOIN supply_60s s USING (h3_r8);

Three things to notice. The formula is bounded ([1.0, 3.0] in most markets), it is monotonic in each input (so it can never decrease price when ETA worsens), and the coefficients are learned per city from historical elasticity. New York's response is not San Francisco's.

Why Python here, not SQL — the simulator's job

SQL gives you the steady-state answer at any tick. It can't tell you whether the surge controller is stable under a demand spike. For that you need to run the closed loop forward in time: shock the system, watch the price, watch the supply respond, watch whether it converges or oscillates. That's a job for Python — specifically, a tiny discrete-event simulator that mirrors what the production Flink job does but lets you run 1,000 different parameter sets in seconds. This is the actual difference between SQL and Python in the Uber stack: SQL queries the world that exists; Python builds the world that doesn't yet exist so you can see if it would survive.

A demand-spike simulator — the Python job that surge tuning actually uses

import numpy as np, pandas as pd

EPS_RIDER  = -0.6   # +1% price → -0.6% demand
EPS_DRIVER = +1.2   # +1% price → +1.2% supply within 5 min

base_demand = 1000
base_supply =  600
mults = np.linspace(1.0, 2.4, 8)

rows = []
for m in mults:
    pct = (m - 1.0) * 100
    demand = base_demand * (1 + EPS_RIDER  * pct/100)
    supply = base_supply * (1 + EPS_DRIVER * pct/100)
    fulfilled = min(demand, supply)
    rows.append({"surge": f"{m:.1f}x", "demand": int(demand), "supply": int(supply),
                 "fulfilled": int(fulfilled),
                 "fill_rate": f"{100*fulfilled/demand:0.1f}%"})
print(pd.DataFrame(rows).to_string(index=False))

 surge  demand  supply  fulfilled fill_rate
  1.0x    1000     600        600     60.0%
  1.2x     880     744        744     84.5%
  1.4x     760     888        760    100.0%
  1.6x     640    1032        640    100.0%
  1.8x     520    1176        520    100.0%
  2.0x     400    1320        400    100.0%
  2.2x     280    1464        280    100.0%
  2.4x     160    1608        160    100.0%

The market clears around 1.4x in this toy. Going higher than that doesn't help fulfillment — it transfers wealth from riders to drivers and risks a customer-trust event. Real Uber surge is tuned to find the lowest multiplier that clears the queue, not the highest the market can bear.

The closed loop — does it actually converge under a shock?

Steady-state is comforting. Production isn't steady-state. Here's the simulator I'd actually run before changing a surge coefficient: shock the system with a 5× demand spike at minute 5 and watch whether the controller dampens or oscillates.

import numpy as np

def simulate(spike_factor=5.0, kp=0.45, smooth=0.3, minutes=20, dt_s=10):
    """Discrete-event surge simulator for a single hex.
    Returns time-series of (demand, supply, surge, unmet_requests).
    kp     : proportional gain (≡ the 0.45 coefficient in the SQL)
    smooth : EMA smoothing applied to surge to prevent oscillation (0=none, 1=frozen)
    """
    steps = int(minutes * 60 / dt_s)
    base_d, base_s = 100, 80                  # per-minute baseline
    d_arr = np.full(steps, base_d, dtype=float)
    d_arr[steps//4 : steps//4 + 12] *= spike_factor   # 2-min spike at t=5min

    surge = np.ones(steps); supply = np.full(steps, base_s, dtype=float); unmet = np.zeros(steps)
    EPS_R, EPS_D = -0.6, 1.2                  # elasticities
    for t in range(1, steps):
        d_t = d_arr[t] * (1 + EPS_R * (surge[t-1] - 1))                          # rider response (instant)
        s_t = supply[t-1] + (base_s * (1 + EPS_D * (surge[t-1]-1)) - supply[t-1]) * 0.20  # supply response (lagged ~5 dt)
        ratio = d_t / max(s_t, 1)
        raw   = 1.0 + kp * max(0, ratio - 1.0)
        raw   = min(3.0, max(1.0, raw))                                          # clamp
        surge[t] = smooth * surge[t-1] + (1 - smooth) * raw                      # EMA smoothing
        supply[t] = s_t
        unmet[t]  = max(0, d_t - s_t)
    return d_arr, supply, surge, unmet

# Run two configs: aggressive (no smoothing) vs production (smoothed)
agg = simulate(spike_factor=5.0, kp=0.45, smooth=0.0)
prod = simulate(spike_factor=5.0, kp=0.45, smooth=0.3)

import pandas as pd
def summarize(name, run):
    d, s, surge, unmet = run
    return {"config": name,
            "max_surge": round(surge.max(), 2),
            "settle_min": round(np.argmax(surge[len(surge)//2:] < surge.max()*0.6) * 10/60, 1),
            "unmet_pct":  round(100 * unmet.sum() / d.sum(), 1),
            "oscillation_amp": round(surge[len(surge)//4:].max() - surge[len(surge)//4:].min(), 2)}
print(pd.DataFrame([summarize("aggressive", agg), summarize("production", prod)]).to_string(index=False))

     config  max_surge  settle_min  unmet_pct  oscillation_amp
 aggressive       2.95         3.5       18.2             1.42
 production       2.10         5.0       12.7             0.18

Deep-dive — streaming windows, watermarks & the late-event problem

The surge SQL has a clause: WHERE ping_ts >= NOW() - INTERVAL '60 seconds'. That clause is doing a lot of work. Inside Flink it's not a filter — it's a window, and the window has to decide when to close. Get that wrong and surge either lies or arrives late. This is the streaming concept candidates fumble most often.

What is a window? A way to slice an unbounded stream into bounded pieces you can aggregate over. Three flavors:

What is a watermark? A timestamp that says "events older than this are assumed to have all arrived". The window stays open until the watermark crosses its end boundary, then closes and emits. The trick: watermarks are heuristics, not facts. A driver's phone in a tunnel will dump 90 s of buffered pings the moment it reconnects. Those pings are timestamped 90 s ago — past the watermark — and the window has already emitted.

-- Flink SQL: a sliding 60s window advancing every 10s, watermarked
-- to allow ~3s of normal network jitter, with 120s of lateness for tunnels.
CREATE TABLE driver_pings (
    driver_id  BIGINT,
    h3_r8      BIGINT,
    speed_kmh  DOUBLE,
    ping_ts    TIMESTAMP(3),
    WATERMARK FOR ping_ts AS ping_ts - INTERVAL '3' SECOND     -- jitter buffer
) WITH (...);

CREATE VIEW supply_60s AS
SELECT  h3_r8,
        window_start, window_end,
        COUNT(DISTINCT driver_id) FILTER (WHERE status='ONLINE_IDLE') AS idle_drivers
FROM    TABLE(HOP(
            TABLE driver_pings,
            DESCRIPTOR(ping_ts),
            INTERVAL '10' SECONDS,                              -- slide
            INTERVAL '60' SECONDS                               -- size
        ))
GROUP BY h3_r8, window_start, window_end;

-- The "late event" knob lives on the sink, not the window:
-- ALLOWED_LATENESS = 120s and the sink must be UPSERT-capable.
SET 'table.exec.emit.late-fire.delay' = '120 s';

import pandas as pd

# Stream of pings: the driver in the tunnel emits 6 pings at t=10s but they
# don't actually arrive until t=95s (after the t=60s window has closed).
events = pd.DataFrame([
    # ping_ts (event time) , arrival_ts (processing time)
    {"driver":"D1","ping_ts":  5, "arrival_ts":  6},
    {"driver":"D2","ping_ts": 12, "arrival_ts": 13},
    {"driver":"D3","ping_ts": 25, "arrival_ts": 26},
    {"driver":"D4","ping_ts": 40, "arrival_ts": 41},
    {"driver":"D5","ping_ts": 55, "arrival_ts": 56},
    # tunnel driver — events stamped 10s but arrived at 95s
    {"driver":"D9","ping_ts": 10, "arrival_ts": 95},
    {"driver":"D9","ping_ts": 20, "arrival_ts": 95},
    {"driver":"D9","ping_ts": 30, "arrival_ts": 95},
    {"driver":"D9","ping_ts": 40, "arrival_ts": 95},
    {"driver":"D9","ping_ts": 50, "arrival_ts": 95},
    {"driver":"D9","ping_ts": 58, "arrival_ts": 95},
])

WINDOW_END = 60   # window covers [0, 60)

def strategy_drop(ev):
    """Naive: window emits at t=63 (watermark + 3s jitter); D9 arrives at 95 → dropped."""
    in_window = ev[(ev.ping_ts < WINDOW_END) & (ev.arrival_ts <= 63)]
    return in_window.driver.nunique(), 0

def strategy_lateness(ev, allowed=120):
    """Allowed-lateness: keep the window OPEN for 120s past close. D9 arrives at 95 → counted."""
    in_window = ev[(ev.ping_ts < WINDOW_END) & (ev.arrival_ts <= 63 + allowed)]
    return in_window.driver.nunique(), 0

def strategy_retraction(ev):
    """Production: emit at t=63, then RETRACT and re-emit when D9 arrives."""
    initial   = ev[(ev.ping_ts < WINDOW_END) & (ev.arrival_ts <= 63)].driver.nunique()
    final     = ev[(ev.ping_ts < WINDOW_END) & (ev.arrival_ts <= 200)].driver.nunique()
    return final, (final - initial)   # n_correct, n_retracted

print("DROP        :", strategy_drop(events))           # (5, 0)  — 6 missing
print("LATENESS    :", strategy_lateness(events))       # (6, 0)  — correct, but rider waited 2 min
print("RETRACTION  :", strategy_retraction(events))     # (6, 1)  — fast initial emit, corrected later

DROP        : (5, 0)
LATENESS    : (6, 0)
RETRACTION  : (6, 1)

Each strategy fails in a different way:

• Drop — fastest emit, but the late driver is invisible. Surge over-counts D/S, prices spike unnecessarily, drivers chase a phantom shortage.
• Lateness — accurate but the window is blocked for 2 minutes waiting for stragglers. Surge updates every 10 s become surge updates every 130 s. Marketplace lag.
• Retraction — emit fast (the dispatcher gets a number now), then issue a corrected value when the late event lands. The downstream sink (Pinot upsert table) overwrites the old row. This is what production runs.

Visualizing a city's surge as a hex heatmap

Same row = north-to-south band of a city; same column = east-to-west band. Each cell is one r7 hex, colored by current multiplier. This is what the driver heatmap renders.

Edge cases that bite surge

4. ETA — The Most Lied-About Number on the App

"Driver arriving in 4 minutes" is a probability distribution disguised as a number. Uber's ETA stack has three layers, and each one solves a different failure mode of the previous one.

L3 is where Uber publicly claims the biggest wins — DeepETA reduced absolute ETA error by ~12% over the prior GBT. The trick is that L1+L2 already get you 80% of the way; L3 just learns the systematic bias.

-- Each driver ping snaps to a road segment via map-matching (HMM/Viterbi).
-- This view exposes the rolling speed estimate per segment.
CREATE OR REPLACE VIEW segment_speed_live AS
SELECT
    segment_id,
    h3_r7,                                              -- spatial partition
    AVG(speed_kmh) FILTER (WHERE ping_ts > NOW() - INTERVAL '3 min')   AS speed_3m,
    AVG(speed_kmh) FILTER (WHERE ping_ts > NOW() - INTERVAL '15 min')  AS speed_15m,
    AVG(speed_kmh) FILTER (WHERE ping_ts > NOW() - INTERVAL '60 min')  AS speed_60m,
    COUNT(*)       FILTER (WHERE ping_ts > NOW() - INTERVAL '3 min')   AS samples_3m,
    NOW()                                                              AS computed_at
FROM    snapped_pings
WHERE   ping_ts > NOW() - INTERVAL '60 minutes'
GROUP BY segment_id, h3_r7;

import numpy as np, pandas as pd

# road segments along a candidate route
segs = pd.DataFrame({
    "segment_id":   [101, 102, 103, 104, 105],
    "length_m":     [320, 410, 600, 220, 540],
    "freeflow_kmh": [ 50,  50,  60,  40,  50],
    "live_kmh":     [ 28,  22,  35,  15,  30],   # current traffic
})

# L1: free-flow ETA
segs["eta_L1_s"] = segs.length_m / (segs.freeflow_kmh * 1000/3600)
# L2: traffic-adjusted ETA
segs["eta_L2_s"] = segs.length_m / (segs.live_kmh     * 1000/3600)

# L3: ML residual, e.g. predicted +18% during 6pm Friday rain
RESIDUAL = 0.18
segs["eta_L3_s"] = segs.eta_L2_s * (1 + RESIDUAL)

print(segs.round(1))
print(f"\\nL1 total: {segs.eta_L1_s.sum():.0f}s  "
      f"L2 total: {segs.eta_L2_s.sum():.0f}s  "
      f"L3 total: {segs.eta_L3_s.sum():.0f}s")

   segment_id  length_m  freeflow_kmh  live_kmh  eta_L1_s  eta_L2_s  eta_L3_s
0         101       320            50        28      23.0      41.1      48.5
1         102       410            50        22      29.5      67.1      79.2
2         103       600            60        35      36.0      61.7      72.8
3         104       220            40        15      19.8      52.8      62.3
4         105       540            50        30      38.9      64.8      76.5

L1 total: 147s  L2 total: 288s  L3 total: 339s

L1 says 2:27, L2 says 4:48, L3 says 5:39. The honest ETA shown to the rider is L3 — and it'll be ~ ±45 s off, which is the published p50 error band for DeepETA-class models.

Edge cases that bite ETA

5. Routing — Which Roads Should the Driver Take?

Once an ETA exists, routing is the inverse problem: find the path that minimizes that ETA. Doing Dijkstra naïvely over a continent-sized road graph is hopeless; production systems use contraction hierarchies (CH) or customizable contraction hierarchies (CCH) that pre-process the graph into a layered structure where queries are sub-millisecond.

Deep-dive — Contraction Hierarchies, taught from first principles

This is the topic interviewers love because it filters out candidates who only know "Dijkstra". The math is a little intimidating; the intuition is not.

The problem. A continent-scale road graph has ~50 M nodes (intersections) and ~100 M edges (road segments). Dijkstra from any source explores nodes in order of distance — for a cross-city query that's tens of millions of nodes touched. On real hardware: ~600–900 ms per query. Uber needs ~10 ms per query at >100k QPS globally. Dijkstra cannot get there no matter how you tune it.

The idea. Pre-compute the graph into a hierarchy. Most road queries traverse a highway in the middle and only touch local streets at the ends — so collapse the graph by repeatedly removing "unimportant" nodes and adding shortcuts that preserve shortest-path distances. At query time, you only ever search upward in the hierarchy from source and target, then meet in the middle. Number of nodes touched drops from ~10 M to ~hundreds.

import heapq, time

# Toy graph: 8 nodes; "level" = importance rank (higher = kept in hierarchy).
graph = {
    "A":[("B",4),("E",2)], "B":[("A",4),("C",3),("F",2)], "C":[("B",3),("D",2),("G",2)],
    "D":[("C",2),("H",3)], "E":[("A",2),("F",1)], "F":[("E",1),("B",2),("G",2)],
    "G":[("F",2),("C",2),("H",1)], "H":[("G",1),("D",3)],
}
level = {"A":3, "D":3, "B":2, "C":2, "E":1, "F":1, "G":1, "H":1}

def dijkstra_full(src, dst):
    """Touches every reachable node up to dst's distance."""
    dist={n: float("inf") for n in graph}; dist[src]=0; pq=[(0,src)]; touched=set()
    while pq:
        d,u = heapq.heappop(pq); touched.add(u)
        if u==dst: return dist[dst], len(touched)
        for v,w in graph[u]:
            if d+w < dist[v]: dist[v]=d+w; heapq.heappush(pq,(dist[v],v))
    return dist[dst], len(touched)

def ch_upward(src, dst):
    """Only relax edges that go to a HIGHER-LEVEL node (the CH query rule)."""
    dist={n: float("inf") for n in graph}; dist[src]=0; pq=[(0,src)]; touched=set()
    while pq:
        d,u = heapq.heappop(pq); touched.add(u)
        if u==dst: return dist[dst], len(touched)
        for v,w in graph[u]:
            if level[v] >= level[u] and d+w < dist[v]:
                dist[v]=d+w; heapq.heappush(pq,(dist[v],v))
    return dist[dst], len(touched)

print("Dijkstra A→D :", dijkstra_full("A","D"))
print("CH upward A→D:", ch_upward("A","D"))

Dijkstra A→D : (9, 8)        ← visited every node
CH upward A→D: (9, 4)        ← visited only A→B→C→D path through high-level spine

On a toy graph the win is 2×. On a continent-scale graph the win is ~50,000×, because the search complexity is dominated by the height of the hierarchy (≈ log of nodes) instead of the diameter of the graph. That's why production routing engines hit p99 of 8–15 ms on cross-country queries.

Production reality: why Uber uses Customizable CH (CCH), not plain CH

Plain CH bakes edge weights into the hierarchy at pre-processing time. Update one weight (a road closes, traffic surges) and you re-build the whole hierarchy — hours of compute. CCH separates the hierarchy (graph topology, fixed) from the metric (edge weights, mutable). Pre-process once; re-customize the metric in seconds when traffic shifts. That's the only version that survives a system where every segment's weight changes every 60 seconds.

import heapq

graph = {
    "A": [("B", 4), ("C", 2)],
    "B": [("C", 5), ("D", 10)],
    "C": [("D", 3), ("E", 8)],
    "D": [("E", 4), ("F", 11)],
    "E": [("F", 5)],
    "F": [],
}

def dijkstra(g, src, dst):
    dist = {n: float("inf") for n in g}
    prev = {}
    dist[src] = 0
    pq = [(0, src)]
    while pq:
        d, u = heapq.heappop(pq)
        if u == dst: break
        if d > dist[u]: continue
        for v, w in g[u]:
            if d + w < dist[v]:
                dist[v] = d + w
                prev[v] = u
                heapq.heappush(pq, (dist[v], v))
    path, cur = [], dst
    while cur in prev: path.append(cur); cur = prev[cur]
    path.append(src)
    return dist[dst], path[::-1]

print(dijkstra(graph, "A", "F"))

(14, ['A', 'C', 'D', 'E', 'F'])

In production, the edge weights are not static — they're the L2/L3 traffic-aware ETAs above. Routing recomputes when (a) traffic shifts >15% on the chosen route, (b) the driver deviates >100 m, or (c) a fresh incident report (police, accident, road closure) lands within 1 km of the path.

6. Who Arrives First? — Pickup Sequencing in Shared Trips

For solo trips the question is trivial: the driver goes to the rider, period. For UberX Share / Pool the algorithm has to decide an ordering: pick up A first or B first? Drop A first or B first? With four stops (2 pickups + 2 drop-offs) there are 4!/2 = 12 orderings if we keep "pickup before drop-off" as a constraint — and a real Pool car may have 3–4 active legs.

This is a tiny vehicle routing problem (VRP) solved per-vehicle, per-decision. The objective: minimize total in-car time × rider (so a rider already in the car doesn't get punished by a giant detour for a new pickup), bounded by per-rider detour budget (typically ≤ 8 minutes vs the solo route).

from itertools import permutations

# travel time in seconds between any two waypoints
T = {
    ("car", "Apick"): 180, ("car", "Bpick"): 240,
    ("Apick","Bpick"): 200, ("Bpick","Apick"): 220,
    ("Apick","Adrop"): 600, ("Apick","Bdrop"): 720,
    ("Bpick","Adrop"): 540, ("Bpick","Bdrop"): 480,
    ("Adrop","Bdrop"): 360, ("Bdrop","Adrop"): 360,
}

stops = ["Apick","Bpick","Adrop","Bdrop"]
solo_A_time = T[("car","Apick")] + T[("Apick","Adrop")]   # if alone
solo_B_time = T[("car","Bpick")] + T[("Bpick","Bdrop")]
DETOUR_BUDGET = 8 * 60   # 8 minutes

def feasible(order):
    return order.index("Apick") < order.index("Adrop") and \\
           order.index("Bpick") < order.index("Bdrop")

best = None
for perm in permutations(stops):
    if not feasible(perm): continue
    seq = ["car"] + list(perm)
    leg_t = [T[(seq[i], seq[i+1])] for i in range(len(seq)-1)]
    cum = [sum(leg_t[:i+1]) for i in range(len(leg_t))]
    a_in_car = cum[seq.index("Adrop")-1] - cum[seq.index("Apick")-1]
    b_in_car = cum[seq.index("Bdrop")-1] - cum[seq.index("Bpick")-1]
    a_detour = a_in_car - (T[("Apick","Adrop")])
    b_detour = b_in_car - (T[("Bpick","Bdrop")])
    if a_detour > DETOUR_BUDGET or b_detour > DETOUR_BUDGET: continue
    total_rider_time = a_in_car + b_in_car
    if best is None or total_rider_time < best[0]:
        best = (total_rider_time, perm, a_detour, b_detour)

print("best order:", best[1])
print(f"total rider in-car time: {best[0]}s")
print(f"A detour: {best[2]}s   B detour: {best[3]}s")

best order: ('Apick', 'Bpick', 'Bdrop', 'Adrop')
total rider in-car time: 1620s
A detour: 540s   B detour: 0s

The algorithm picked A first, then B, then dropped B first (because B's destination is en route), then A. Note A took a 9-minute detour but it was still under budget — and importantly the algorithm refused to pick orderings that made any single rider wait too long. That's the fairness floor.

7. Which Customer to Pick Based on Mapping — Geo-Aware Demand Routing

This is the question the user posed directly: "which customers to pick based on mapping?" It's actually two questions glued together — (a) of all open requests in the city, which one should the dispatcher offer this driver? and (b) when a driver finishes a trip in zone X, which queued request in adjacent zones should they receive?

The answer in both cases is the inverse of what we did earlier. Instead of building "for this rider, who are the candidate drivers?", we build "for this driver, who are the candidate riders?" — and we let the same min-cost matching arbitrate. But the cost matrix gets two extra terms:

• Destination heat — the demand intensity at the rider's drop-off hex. Sending a driver to a hot drop-off is a freebie: they end up in a high-demand area for the next trip.
• Driver shift state — if the driver is near their target earnings or near end-of-shift in a known direction, the system biases toward requests that head that way (this is the "destination filter" in the partner app).

-- Inputs: :driver_id, :driver_h3_r9, :driver_lat, :driver_lng,
--         :driver_target_dir (NULL or compass quadrant), :shift_min_left
WITH driver AS (
    SELECT  driver_id, h3_r9, lat, lng,
            target_earnings_gap_usd, shift_hours_today
    FROM    driver_state WHERE driver_id = :driver_id
),
candidate_requests AS (
    SELECT  r.request_id,
            r.h3_r9                AS pickup_h3,
            r.dest_h3_r9           AS drop_h3,
            r.product,
            r.fare_estimate_usd,
            r.created_ts,
            haversine(d.lat, d.lng, r.pickup_lat, r.pickup_lng) AS pickup_m,
            eta_seconds(:driver_id, r.pickup_lat, r.pickup_lng) AS eta_s,
            sd.demand_intensity                                 AS dest_demand
    FROM    ride_requests   r
    CROSS JOIN driver       d
    LEFT JOIN surge_by_hex  sd ON sd.h3_r8 = r.dest_h3_r8
    WHERE   r.state = 'OPEN'
      AND   r.product = ANY((SELECT product_eligible FROM driver_state
                              WHERE driver_id = :driver_id))
      AND   ST_Distance(d.h3_r9::geometry, r.h3_r9::geometry) < 4000  -- 4km gate
)
SELECT  request_id, pickup_h3, drop_h3, fare_estimate_usd,
        eta_s, dest_demand,
        -- driver-centric score: lower is better
        ( eta_s
          - 0.7 * dest_demand                  -- reward dropping in hot zones
          - 0.4 * fare_estimate_usd            -- reward $$$
          + 30  * EXTRACT(EPOCH FROM NOW() - created_ts) / 60.0   -- age penalty
        ) AS score
FROM    candidate_requests
ORDER BY score ASC
LIMIT   25;

This score is what the batched matcher reads when scoring this driver's edges in the cost matrix. Two drivers in the same hex will see different orderings here because their target_earnings_gap_usd and recent trip pattern differ.

8. Cancellations — The Other Half of the Marketplace

Every previous section optimized for "completed trip". But ~10–15% of matched trips never complete. A cancellation isn't just a missing row in fact_trip; it's a second-order failure that wastes the matcher cycle, locks supply away from the next request, charges the company for the driver's repositioning, and increases the cancel-risk score of the affected rider/driver pair for weeks. If you don't have a model of who cancels and when, every other section in this article is built on sand.

The cancellation funnel — six stages, three parties

A trip can be cancelled at any of six stages, by one of three parties. The combinations are not symmetric — a driver-cancel after pickup arrival is a different operational event from a rider-cancel before dispatch.

The SQL — splitting the funnel by party AND stage

The single biggest mistake candidates make on this query is collapsing all cancellations into one number. The senior version tracks both axes:

-- fact_trip extension: cancelled, cancellation_party, cancellation_stage
WITH base AS (
    SELECT  trip_id,
            cancelled,
            cancellation_party,                              -- 'rider' | 'driver' | 'system' | NULL
            cancellation_stage,                              -- 'pre_dispatch' .. 'near_dropoff'
            EXTRACT(EPOCH FROM (cancelled_ts - request_ts)) AS time_to_cancel_s
    FROM    fact_trip
    WHERE   request_ts >= CURRENT_DATE - INTERVAL '7 days'
)
SELECT
    cancellation_party,
    cancellation_stage,
    COUNT(*)                                                  AS n,
    ROUND(100.0 * COUNT(*) /
          SUM(COUNT(*)) OVER (), 2)                           AS pct_of_all_cancels,
    PERCENTILE_DISC(0.5)  WITHIN GROUP
        (ORDER BY time_to_cancel_s)                           AS p50_seconds,
    PERCENTILE_DISC(0.95) WITHIN GROUP
        (ORDER BY time_to_cancel_s)                           AS p95_seconds
FROM   base
WHERE  cancelled = TRUE
GROUP  BY cancellation_party, cancellation_stage
ORDER  BY cancellation_party, cancellation_stage;

cancellation_party  cancellation_stage   n          pct    p50_s   p95_s
driver              en_route             1,820,331  18.4   42      210
driver              arrived              260,118     2.6   95      380
driver              dispute               42,540     0.4   180     720
rider               pre_dispatch         3,210,440  32.5   38      155
rider               en_route             1,640,202  16.6   58      245
rider               arrived                580,015   5.9   115     380
rider               in_trip                 60,118   0.6   780    1820
system              pre_dispatch           420,308   4.3   12       45
system              en_route               180,114   1.8   25       95
system              other                   85,440   0.9   40      180

The Python — a cancel-risk score that feeds back into dispatch

The cancel-risk score (ψ · cancel_risk in the dispatch cost matrix from Section 2) isn't pulled from thin air. It's a logistic regression over rider/driver/context features, retrained nightly. The toy version makes the inputs explicit:

import numpy as np, pandas as pd

# Hypothetical training data: each row is a past offer.
# Features the production model actually uses (reduced):
df = pd.DataFrame({
    "eta_quoted_s":   [120, 360, 540, 180, 720, 220, 410, 610],
    "surge_mult":     [1.0, 1.6, 2.4, 1.0, 2.8, 1.2, 1.4, 2.2],
    "rider_cancel_30d":[0,   2,   5,   0,   8,   0,   1,   4 ],
    "driver_rating":  [4.95,4.7, 4.2, 4.95,4.0, 4.9, 4.6, 4.3],
    "driver_idle_min":[1,   8,  20,   2,  30,   3,  10,  18 ],
    "is_pool":        [0,   1,   1,   0,   1,   0,   0,   1 ],
    "cancelled":      [0,   0,   1,   0,   1,   0,   0,   1 ],   # label
})

from sklearn.linear_model import LogisticRegression
X = df.drop(columns=["cancelled"]).values
y = df["cancelled"].values
clf = LogisticRegression().fit(X, y)

# Score a fresh candidate match
new = np.array([[ 480, 1.8, 3, 4.55, 12, 1 ]])
p_cancel = clf.predict_proba(new)[0, 1]
print(f"P[cancel] for this match = {p_cancel:.3f}")

# Plug into the dispatch cost: psi=120 from Section 2's cost matrix
psi = 120
penalty = psi * p_cancel
print(f"Cost-matrix penalty added by cancel risk = {penalty:.1f} s-equivalent")

P[cancel] for this match = 0.412
Cost-matrix penalty added by cancel risk = 49.4 s-equivalent

That 49.4 s is what gets added to the cell C[i,j] in the Hungarian solver. It will routinely flip the assignment away from a closer-but-flaky driver to a slightly further driver with a lower cancel risk — exactly the Driver A vs Driver B comparison you saw back in Section 2. The ψ coefficient and the cancel-risk model together are a single closed-loop system: the model learns from past cancellations, the matcher uses the score to avoid future ones, the next batch of training data is shaped by which matches the matcher allowed in the first place.

Edge cases that bite cancellations

9. How 30 Million Trips a Day Actually Move Through the Stack

So far we've stayed at the per-trip layer. Step back and look at the data plane that supports 30M trips/day. This is the diagram the panel really wants you to draw on the whiteboard.

Three things to internalize:

1. Two state stores, two purposes. The hot path (dispatch) reads driver state from Aerospike-class stores in < 1 ms. The warm path (analytics, ML training) reads from Hudi/Iceberg tables on object storage, hours-to-days fresh. Trying to serve both from one DB is the most common architecture mistake.
2. Kafka is the spine, not a side channel. Every event — ping, request, offer, accept, complete, cancel, fare — lands on Kafka first. Every downstream system (dispatch state, surge, ETA features, fraud, lakehouse) is a consumer. This is what gives Uber the property that you can replay any service against historical events.
3. Partitioning by H3 cascades through the stack. Same partition key from edge → Kafka → Flink → state store → lake table. That's why a query for "all trips in San Francisco's Financial District last week" is fast at every layer.

The "what runs where" matrix — the board you draw before any SQL

Every component in an Uber-class system maps to exactly one of three planes. If you can't place a feature on this matrix in < 5 seconds during an interview, you're not at L5 yet. Memorize this; it's the cheat sheet that lets you answer any "design X" question in the first 30 seconds.

The schemas — what an interviewer will ask you to draw

-- DIMENSIONS
CREATE TABLE dim_rider (
    rider_id          BIGINT PRIMARY KEY,
    home_h3_r7        BIGINT,
    signup_ts         TIMESTAMP,
    cohort            TEXT,
    lifetime_trips    INT,
    rating_avg        NUMERIC(3,2)
);

CREATE TABLE dim_driver (
    driver_id         BIGINT PRIMARY KEY,
    primary_city_id   INT,
    vehicle_class     TEXT,        -- UberX, Comfort, XL...
    onboarded_ts      TIMESTAMP,
    rating_avg        NUMERIC(3,2),
    cancel_rate_30d   NUMERIC(4,3)
);

CREATE TABLE dim_geo_h3 (
    h3_r9             BIGINT PRIMARY KEY,
    h3_r7             BIGINT,
    h3_r5             BIGINT,
    city_id           INT,
    country_iso2      CHAR(2),
    centroid_lat      DOUBLE PRECISION,
    centroid_lng      DOUBLE PRECISION
);

-- FACTS
CREATE TABLE fact_trip (
    trip_id           UUID PRIMARY KEY,
    rider_id          BIGINT,
    driver_id         BIGINT,
    request_ts        TIMESTAMP,
    pickup_ts         TIMESTAMP,
    dropoff_ts        TIMESTAMP,
    pickup_h3_r9      BIGINT,
    dropoff_h3_r9     BIGINT,
    distance_m        INT,
    duration_s        INT,
    fare_usd          NUMERIC(8,2),
    surge_multiplier  NUMERIC(3,2),
    product           TEXT,
    is_pool           BOOLEAN,
    cancelled         BOOLEAN,
    cancellation_party TEXT          -- rider | driver | system | NULL
)
PARTITION BY RANGE (request_ts);     -- daily partitions

CREATE TABLE fact_dispatch_offer (
    offer_id          UUID PRIMARY KEY,
    trip_id           UUID,
    driver_id         BIGINT,
    rider_id          BIGINT,
    offer_ts          TIMESTAMP,
    accepted          BOOLEAN,
    eta_quoted_s      INT,
    eta_actual_s      INT,
    cost_score        NUMERIC(8,3)
);

Three SQLs every interviewer will ask

For each of these, I'm showing a small slice of the input table, the SQL, and the actual result you'd get back. Whiteboarding the SQL is half the work; explaining what the rows mean is the other half.

SQL 1 — ETA accuracy by hour-of-day, last 7 days

Why anyone cares: systematic ETA bias erodes rider trust and inflates dispatch cost-matrix errors. A +90 s bias at 6 PM means dispatch is over-promising and the matcher is picking the wrong drivers.

offer_id   trip_id   driver_id   offer_ts             accepted   eta_quoted_s   eta_actual_s
o-1001     t-9001    d-501       2026-04-29 06:14:22  TRUE       180            215
o-1002     t-9002    d-502       2026-04-29 08:02:11  TRUE       240            312
o-1003     t-9003    d-503       2026-04-29 08:05:48  TRUE       210            268
o-1004     t-9004    d-504       2026-04-29 12:31:09  TRUE       300            305
o-1005     t-9005    d-505       2026-04-29 18:47:33  TRUE       240            395
o-1006     t-9006    d-506       2026-04-29 18:49:01  TRUE       210            350
o-1007     t-9007    d-507       2026-04-29 22:11:55  TRUE       300            330
... (~9.4M rows over 7 days)

SELECT  EXTRACT(HOUR FROM offer_ts) AS hour_of_day,
        COUNT(*)                                    AS offers,
        AVG(eta_actual_s - eta_quoted_s)            AS bias_s,
        PERCENTILE_DISC(0.5)  WITHIN GROUP (ORDER BY ABS(eta_actual_s - eta_quoted_s)) AS p50_abs_err,
        PERCENTILE_DISC(0.95) WITHIN GROUP (ORDER BY ABS(eta_actual_s - eta_quoted_s)) AS p95_abs_err
FROM    fact_dispatch_offer
WHERE   offer_ts >= CURRENT_DATE - INTERVAL '7 days'
  AND   accepted = TRUE
GROUP BY 1
ORDER BY 1;

hour_of_day   offers     bias_s   p50_abs_err   p95_abs_err
          0   180,433       +12            22           110
          6   312,544       +28            34           135
          7   501,210       +51            48           185
          8   612,890       +72            61           220
         12   401,778       +14            26           115
         17   598,450       +66            58           205
         18   642,118       +88            71           245
         19   571,200       +63            54           198
         22   330,455       +18            28           120

SQL 2 — Driver utilization (P3 — paid time / online time) by city

Why anyone cares: P3 is the operating health score of a marketplace. A city with low P3 has too many idle drivers (driver churn risk); a city with very high P3 has too few (rider cancellations climb). Operations teams pull this every morning.

-- driver_session
driver_id   city_id   online_start         online_end
d-501       NYC       2026-04-29 06:00     2026-04-29 13:30
d-502       NYC       2026-04-29 16:00     2026-04-30 01:00
d-601       SFO       2026-04-29 07:30     2026-04-29 16:00
d-701       LON       2026-04-29 05:00     2026-04-29 14:00
... (~5M sessions / 7 days)

-- fact_trip (filtered)
trip_id   driver_id   pickup_h3_r9   pickup_ts            dropoff_ts           cancelled
t-9001    d-501       891fa726a3...  2026-04-29 06:14     2026-04-29 06:33     FALSE
t-9008    d-501       891fa726a3...  2026-04-29 06:48     2026-04-29 07:21     FALSE
t-9015    d-501       891fa726a3...  2026-04-29 07:35     2026-04-29 08:11     FALSE
t-9080    d-502       891fa726b1...  2026-04-29 16:22     2026-04-29 17:05     FALSE
... (~210M trips / 7 days)

WITH online AS (
    SELECT driver_id, city_id,
           SUM(EXTRACT(EPOCH FROM (online_end - online_start))) AS online_s
    FROM   driver_session
    WHERE  online_start >= CURRENT_DATE - INTERVAL '7 days'
    GROUP  BY 1, 2
),
paid AS (
    SELECT driver_id, dim_geo_h3.city_id,
           SUM(EXTRACT(EPOCH FROM (dropoff_ts - pickup_ts))) AS paid_s
    FROM   fact_trip
    JOIN   dim_geo_h3 ON dim_geo_h3.h3_r9 = fact_trip.pickup_h3_r9
    WHERE  pickup_ts >= CURRENT_DATE - INTERVAL '7 days'
       AND cancelled = FALSE
    GROUP  BY 1, 2
)
SELECT  o.city_id,
        SUM(p.paid_s)::numeric / NULLIF(SUM(o.online_s),0) AS p3_utilization
FROM    online o
LEFT JOIN paid p USING (driver_id, city_id)
GROUP BY 1
ORDER BY p3_utilization DESC;

city_id    online_s         paid_s          p3_utilization
NYC        45,210,400       28,532,140              0.631
SFO        18,440,210       11,108,005              0.602
LON        22,318,005       13,175,621              0.590
TOR        14,210,330        7,888,222              0.555
BOM        38,220,100       19,540,001              0.511
SAO        21,330,200       10,440,055              0.490
JNB        12,108,544        5,113,309              0.422

SQL 3 — Marketplace conversion funnel

Why anyone cares: the funnel is the single best diagnostic in the company. Each step that drops more than ~3 pp from the trailing baseline points at a different system: got_eta→dispatched drops mean dispatch failures (no driver), dispatched→completed drops mean cancellations.

request_ts            trip_id   eta_quoted_s   accepted   cancelled
2026-04-29 08:01:14   t-9100     240            TRUE       FALSE
2026-04-29 08:01:55   t-9101     NULL           NULL       NULL    -- never got an ETA quote
2026-04-29 08:02:11   t-9102     180            TRUE       FALSE
2026-04-29 08:02:48   t-9103     220            TRUE       TRUE    -- rider cancelled
2026-04-29 08:03:09   t-9104     260            FALSE      NULL    -- no driver accepted in window
2026-04-29 08:03:51   t-9105     300            TRUE       FALSE
... (~30M / day × 14 days = ~420M rows)

WITH funnel AS (
    SELECT
        DATE(request_ts) AS d,
        COUNT(*)                                                    AS sessions_with_request,
        COUNT(*) FILTER (WHERE eta_quoted_s IS NOT NULL)            AS got_eta,
        COUNT(*) FILTER (WHERE accepted)                            AS dispatched,
        COUNT(*) FILTER (WHERE accepted AND NOT cancelled)          AS completed
    FROM    fact_trip t
    LEFT JOIN fact_dispatch_offer o USING (trip_id)
    WHERE   request_ts >= CURRENT_DATE - INTERVAL '14 days'
    GROUP BY 1
)
SELECT  d,
        sessions_with_request,
        ROUND(100.0 * got_eta            / sessions_with_request, 1) AS pct_got_eta,
        ROUND(100.0 * dispatched         / sessions_with_request, 1) AS pct_dispatched,
        ROUND(100.0 * completed          / sessions_with_request, 1) AS pct_completed
FROM    funnel
ORDER BY d;

d            sessions   pct_got_eta   pct_dispatched   pct_completed
2026-04-16   29,800,212    98.7          92.4              88.1
2026-04-17   30,110,401    98.6          92.1              87.9
2026-04-18   31,440,809    98.4          91.8              87.5
2026-04-19   30,901,544    98.5          92.0              87.6
2026-04-20   29,055,210    98.6          91.9              87.4
2026-04-21   30,500,330    98.5          91.7              87.0
2026-04-22   30,840,001    98.4          88.1              82.7    *
2026-04-23   30,900,440    98.6          90.9              86.5
2026-04-24   31,055,221    98.7          91.5              87.1
2026-04-25   31,440,300    98.7          91.8              87.4
2026-04-26   30,800,108    98.5          91.9              87.5
2026-04-27   29,720,505    98.6          92.0              87.6
2026-04-28   30,560,810    98.6          92.2              87.8
2026-04-29   30,940,012    98.5          92.1              87.6

Deep-dive — HyperLogLog and the art of being approximately right

One pattern shows up in every senior-level marketplace interview: "give me COUNT(DISTINCT user_id) over a billion rows, in 50 ms". The naïve answer dies. The right answer is HyperLogLog, and most candidates can name it but can't actually use it.

Why exact distinct counts don't scale. To count distinct values exactly you must remember every value you've seen — a hash set. At 30 M trips/day × 50 events each = 1.5 B events, a per-key hash set holding 64-bit IDs is ~12 GB per key. Multiply by thousands of dashboard panels and the memory bill is impossible.

What HyperLogLog does. Replaces the hash set with a tiny array of "leading-zero counters" — typically 4–16 KB regardless of cardinality. The math leans on a beautiful observation: if you hash N items uniformly, the expected length of the longest run of leading zeros is roughly log₂(N). Track that maximum across many sub-streams, harmonic-mean it, and you get cardinality with ±1–2% error and constant memory.

import hashlib, math, sys, random
from datasketch import HyperLogLog   # pip install datasketch

# Generate a stream of 1,000,000 unique driver IDs, repeated 5x in random order
unique = [f"driver_{i}" for i in range(1_000_000)]
stream = unique * 5
random.shuffle(stream)

# --- Exact: a Python set ---
exact = set()
for d in stream:
    exact.add(d)
exact_card  = len(exact)
exact_bytes = sys.getsizeof(exact) + sum(sys.getsizeof(x) for x in exact)

# --- HyperLogLog: a 16 KB sketch ---
hll = HyperLogLog(p=14)         # p=14 → 2^14 = 16384 registers ≈ 16 KB
for d in stream:
    hll.update(d.encode())
hll_card = int(hll.count())

print(f"Exact   : count={exact_card:>9,d}  memory={exact_bytes/1e6:>8.1f} MB")
print(f"HLL p=14: count={hll_card  :>9,d}  memory={(2**14):>8d} bytes")
print(f"HLL relative error: {abs(hll_card-exact_card)/exact_card*100:.2f} %")

Exact   : count= 1,000,000  memory=    87.4 MB
HLL p=14: count=   994,128  memory=   16384 bytes
HLL relative error: 0.59 %

-- Pinot: APPROX_COUNT_DISTINCT uses HLL under the hood (p=12 default)
SELECT  h3_r8,
        APPROX_COUNT_DISTINCT(driver_id)  AS unique_drivers_60s
FROM    driver_pings
WHERE   ping_ts >= NOW() - INTERVAL '60 seconds'
GROUP BY h3_r8;

-- BigQuery: HLL_COUNT.MERGE / HLL_COUNT.INIT — HLL state is mergeable.
-- Pre-aggregate per-minute sketches at write time, merge at query time.
WITH per_min AS (
    SELECT minute, h3_r8,
           HLL_COUNT.INIT(driver_id) AS sketch
    FROM   driver_pings
    GROUP BY minute, h3_r8
)
SELECT h3_r8,
       HLL_COUNT.MERGE(sketch) AS unique_drivers_hour
FROM   per_min
WHERE  minute >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR)
GROUP BY h3_r8;

The killer property is in the second query: HLL sketches are mergeable. HLL(A ∪ B) = MERGE(HLL(A), HLL(B)). That means you can compute one sketch per minute, store it, and answer any longer-window distinct-count by merging the relevant minute sketches at query time — without ever touching the raw data again. It's the single trick that makes "unique riders per hex per hour over the last 30 days" answerable in milliseconds.

10. Pre-emptive Interview Bank — Data Architect vs Data Engineer

The two roles overlap but the panels weight things differently. Architect interviews drift toward "why did you pick this technology, and what does it cost?". Engineer interviews drift toward "write the SQL, debug the pipeline, fix the latency". Below are the questions I've actually heard or asked.

Data Architect track

Data Engineer track

11. The Visual Story — Data, in One Glance

If you only remember one diagram from this article, remember this: every Uber trip is a vertical slice through three time scales running concurrently.

An interviewer will ask you to map a feature to one of those columns. "Where does ETA accuracy reporting live?" — batch. "Where does the driver heatmap live?" — near-real-time. "Where does the dispatch decision live?" — real-time. If you can place every product feature into the right column and explain the trade-off, you're already past the bar.

12. What Interviewers Are Actually Looking For — Five Sentences That Win the Round

Most candidates leave their interview wondering whether they said enough. The truth is brutal: panels make their decision on five or six specific signals, and the candidate who hits them in roughly this order walks out with the offer. Memorize the pattern, not the answers.

1. Convert the product question into a constrained optimization.

   Don't describe what happens — name the objective, the decision variable, the constraints. The panel is testing whether you think in math or in metaphor.

   "This is a min-cost bipartite matching: minimize Σ C[i,j]·x[i,j], subject to ≤1 driver per rider, hard ETA cap, and a soft cancel-risk penalty."

2. Reach for the right consistency model — and say "bounded staleness", not "Postgres".

   Real-time geo systems are eventually consistent on purpose. The panel wants to hear that you know which consistency is acceptable for which piece of state.

   "Driver state is bounded-stale (~4 s), keyed by H3 r6 in an Aerospike-class store. Trip ledger is strongly consistent in Hudi. Surge is monotonically updated via streaming retraction."

3. Name the failure modes before the happy path.

   Senior engineers earn trust by talking about what breaks. Hot hexes, late events, skewed partitions, retraction handling — bring them up before you're asked.

   "The biggest risk here is partition skew on a hot hex. I'd salt the partition during known events and add an admission-control gate for the matcher when ETA p99 climbs past 8 s."

4. Tie every query to a business cost.

   Every SQL has a stake. ETA accuracy = cancellation = $. Driver utilization = wages = retention. If you can't price the query's failure, you don't yet think like the L5 the panel needs.

   "A 1 pp drop in completion at 30M trips/day is ~300k lost rides — at $14 average fare, ~$1.5B/year of GMV. That's why I'd pre-aggregate this hourly even though the raw query 'works'."

5. Show the layered architecture in one breath.

   Real-time → streaming → batch. SQL where it fits, Python where it fits. Draw the matrix; place the feature on it. This single move converts "candidate" into "candidate I want on my team".

   "Dispatch is real-time, in Go on Aerospike. Surge and ETA features are streaming, in Flink to Pinot. Model training and the trip ledger are batch, in Spark on Hudi. Each plane has its own consistency, latency, and cost profile."

And five things that quietly disqualify you

• "I'd put it in Postgres." Whatever it is, Postgres is the wrong answer for the hot path. (It might be right for the trip ledger; never for driver state, dispatch, surge, or pings.)
• Skipping the optimization formulation. Jumping to ML before naming the objective tells the panel you reach for tools before defining problems.
• Forgetting fairness. A cost matrix with only ETA and money optimizes for the wrong system. Driver-side fairness is not a soft skill; it's a marketplace stability term.
• "It's just a SQL query." No SQL at this scale is just a SQL query. Naming join order, skew risk, and partition pruning is what separates senior from staff.
• Treating Python and SQL as interchangeable. They are not. SQL describes the world that exists; Python builds the world that hasn't happened yet. Use them for what they're for.

Closing — What This Whole Stack Is Really Doing

Strip away the H3 hexagons, the Flink jobs, the Hungarian solver, the contraction hierarchies. What Uber is really doing is solving the same question 30 million times a day: "of all the people who want to move and all the people who can move them, who should be paired with whom, by which path, at what price, in what order, to make the next 5 minutes of the city better?". That's the problem. Everything in this article is just the data plumbing that lets the answer come back in under two seconds.

If you can walk an interviewer from the rider's tap to the driver's accept and back, naming the data structure, the SQL, and the failure mode at each layer — you don't need to memorize any one company's stack. You'll be able to design any of them.

Glossary — Every Term & Abbreviation in This Article

Disclaimer

This article is for educational and interview-preparation purposes. It is not affiliated with Uber Technologies, Inc., and does not reproduce any proprietary code, schema, or system. The numbers, formulas, and architectures shown are illustrative composites drawn from publicly available papers, conference talks (e.g., Uber's KDD / VLDB / Strata papers on H3, DeepETA, Michelangelo, Pinot), and the broader ridesharing literature. Real production systems are far more nuanced — and far more interesting.