SQL & hypothesis testing · 2025
Zuber — Rideshare in Chicago
Does weather actually change how long Loop-to-O’Hare rides take on Saturdays, reliably enough to build into fleet planning and pricing?
Python · BeautifulSoup · SQL (PostgreSQL) · SciPy
The question
Zuber, a ride-sharing company in Chicago, needed to know whether weather is a variable worth building into fleet planning, arrival-time estimates and dynamic pricing — specifically, whether rides from the Loop to O'Hare Airport take longer on rainy or stormy Saturdays than on clear ones.
The data
Chicago weather records for November 2017, scraped from an HTML table via requests and
BeautifulSoup. Trip volume and duration pulled directly with SQL from Zuber's database
(trips, cabs, neighborhoods, weather_records). The hypothesis test itself runs on rides
from the Loop (neighborhood_id 50) to O'Hare (neighborhood_id 63) on Saturdays: 180 rides
logged under "Bad" weather (rain or storm in the description) against 888 under "Good".
Method, and why this method
SQL first: ride counts by taxi company (Nov 15–16, 2017), by company name pattern ("Yellow" or
"Blue", first week of November), and Flash Cab plus Taxi Affiliation Services against everyone
else grouped as "Other" (also first week of November). Weather condition was labelled "Bad"
when the description contained "rain" or "storm" and "Good" otherwise, then joined to trips by
matching timestamp (trips.start_ts = weather_records.ts), filtered to the Loop→O'Hare route
on Saturdays (EXTRACT(DOW FROM start_ts) = 6).
For the hypothesis test: H0 is that average ride duration is the same in Bad and Good weather;
H1 is that it differs. Alpha is 0.05. Before running the test, the variances of the two samples
were checked: 520,294 for Bad against 576,382 for Good — different. Because the
variances differ, the test was run with equal_var=False, which applies Welch's t-test,
adjusting the degrees of freedom for a more reliable result than a standard Student's t-test.
Findings
By company: Flash Cab led with 19,558 rides on November 15–16, almost double the runner-up. Among companies with "Yellow" or "Blue" in the name (first week of November), Yellow Cab (33,668) and Taxi Affiliation Service Yellow (29,213) stood out. Grouping everything but Flash Cab and Taxi Affiliation Services as "Other" for that same week: Other totalled 335,771 rides against 64,084 for Flash Cab and 37,583 for Taxi Affiliation Services — the rest of the market is highly fragmented.
By destination neighbourhood: Loop and River North lead by a wide margin (10,727 and 9,524 average trips respectively), reflecting Chicago's downtown financial/cultural core and its concentration of restaurants and nightlife. O'Hare ranks 5th (2,547) despite being a mostly residential area, on the strength of airport traffic.
The hypothesis test: p = 6.74e-12, far below alpha (0.05), so H0 is rejected — weather affects ride duration on this route. A boxplot confirms it visually: the median ride duration on Bad-weather days exceeds 2,500 seconds, against under 2,000 seconds on Good-weather days. Both groups have outliers; Good-weather outliers exceed 7,000 seconds, possibly traffic incidents or exceptional events on days that were otherwise clear.
The decision it enables
Weather is a variable worth building into Zuber's operations: on rainy Saturdays, Loop-to-O'Hare rides run significantly longer, which bears directly on fleet planning, arrival-time estimates and dynamic pricing for that route.