Section 1: The Ride That Started Too Early - Problem Understanding & Clarifications

Executive Summary: The first ten minutes decide whether you find the real problem. We show that the obvious range query silently drops the rides that matter most, name the actual problem (an interval-overlap query over events that may not have ended yet), run the clarification exchange where every answer changes the design, and introduce the three San Francisco rides that test every later section.

The interviewer reads one sentence off a card:

"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."

It sounds like a time-range lookup, and most candidates have a query on the whiteboard within ninety seconds:

SELECT * FROM rides WHERE start_ts BETWEEN :qs AND :qe;

Hold that query up against a real request. It is Friday, 2026-10-30. A water main breaks on Market Street, and the safety-ops team asks: "Which rides were active in SF between 17:10:00Z and 17:40:00Z?" Maya's ride, trip_8f3a, picked up at 16:52:10Z and dropped off at 17:31:45Z. Maya was in the car for more than twenty minutes of that window, and the query never returns the ride, because it started before the window opened.

The usual patch, OR end_ts BETWEEN :qs AND :qe, still misses two kinds of ride: one that starts before the window and ends after it, and one that hasn't ended at all. The correct test needs both columns:

start_ts <= :qe AND (end_ts IS NULL OR end_ts > :qs)

Key Insight: An event is an interval, [start, end), and "active between" is an overlap test. That is two range conditions on two different columns. A single B-tree index can narrow only one of them, so the other forces a scan over all history before the window or everything that ended after it opened. This mismatch, plus rides that have no end yet, is what the rest of the course is about.

Breaking Down the Prompt

Before asking anything, map the territory out loud:

• What: Every event whose interval overlaps a window, including events still in progress. Not "events that started in the window".
• Who: Safety ops (who was on the road during an incident), insurance and claims (was this trip in progress at the moment of the collision), pricing and marketplace analytics, support, and finance.
• Why: Most callers ask was this happening at that moment? A wrong "no" is the expensive failure: a rider who gets no safety check-in, a claim wrongly denied.
• How: Two shapes. Dashboards ask about the last few minutes, all day; investigations ask about a past window and can return a lot of rows.