Active Events Service System Design

The interviewer's prompt fits on one line: "Design a service that answers one question: given a start time and an end time, return every event that was active in between. This came from Uber: think of every ride that was in progress during a given window, but other teams will want it for their own events too."

The first version writes itself: a rides table, an index on start_ts, and WHERE start_ts BETWEEN :start AND :end. It returns rows, it is fast, and it is wrong. A water main breaks on Market Street in San Francisco, and the safety team asks which rides were active between 17:10 and 17:40. Maya's ride, picked up at 16:52 and dropped off at 17:31, spent twenty-one minutes inside that window, and the query never sees it, because it started before the window opened. Fix the predicate to the correct one, start <= :end AND (end IS NULL OR end > :start), and the index can now bound only one side of it: the database scans every ride that ever started before 17:40. The IS NULL is there because a ride picked up at 17:38 that is still in progress has no end time at all.

The Trap Built Into a Time Range

Two failures come up again and again:

• The Range Scan: treats "active between" as "started between", or writes the right predicate and trusts a B-tree to make it fast. Signal: never noticed that an event is two numbers, and that indexing two numbers at once is the whole problem.
• The Algorithm Recital: draws an interval tree on the whiteboard, proves O(log n + k), and stops there, with a structure that lives in one process's memory, holds 2.7 billion intervals for 90 days, and has no answer for a ride that has not ended yet. Signal: solved the textbook problem, not the production one.

The passing answer treats the query as what it is: an overlap between two intervals, over a stream where the newest intervals are still open. Closed rides go into fixed 15-minute time buckets, one row per bucket a ride touches, so any window is a handful of partition scans with a deterministic rule that returns each ride exactly once. Open rides, about a million of them at peak, live in a small in-memory tier keyed by city and start time. And the seam between the two tiers is ordered so that a ride ending mid-query can be returned twice but never missed.