#273 Data modelling 6.7 min
Data Modelling Interview: Design a Ride Hailing Schema
On one made-up day, a wide table I wrote to fail could not find the busiest hour, overstated one driver's earnings by 54.18, and gave a surge of 1.0 where the schema said 1.8.
Compare the two designs
Copy the code folder first. Run python run.py good, then python run.py poor. Write down the four questions the program asks, and for each one say which design answers it correctly. Everything is made up and runs in memory.
A hint
The poor design is one wide table written to fail in these ways.
Follow a cancelled trip
In run.py, change TRIP = 2 to TRIP = 5 and run python run.py good. Predict how the history differs from trip 2. When I ran it, trip 5 showed requested at 08:40, accepted at 08:42 and cancelled at 08:44, with no completed line, and a surge of 1.8 when requested. Change TRIP back to 2 afterwards, because the next task refers to trip 2.
A hint
A cancelled trip has a different list of statuses.
Change the morning surge
In data.py, change the 1.8 in the SURGE row (1, "08:00", "08:45", 1.8) to 2.5. Predict what the surge line for trip 2 prints, then run python run.py good. When I ran it, the last line read 2.5. Some earnings changed too, because trips requested in that window were quoted at the new surge. Run python run.py poor and see whether its surge line changes.
A hint
The wide table keeps only the current value of each zone.
Add a busy evening hour
In data.py, change the start of HOURS from [7, 8, to [18, 18, 8, so that two more requests fall at 18 and one at 7 is dropped. Predict the busiest hour, then run python run.py good. When I ran it, the busiest hour was 18 with 6 trips. The earnings change too, because the random trips are made again.
A hint
The busiest hour query groups requests by the hour of the request.
These tasks are for you to try on your own machine. Nothing is checked or saved.
What this does not show
One made-up day of 18 trips; the wide table was written to show these failures; the interview questions are my own choice; no platform commission in earnings; no time zones; ratings are stored but not queried; not any real company's design.
Up next in Data modelling
More in Data modelling
Related videos
- #133 A/B Test Analysis in SQL and Python: z Test and Peeking SQL and analytics
- #157 Model Registry by Hand: Versions, Promote, Roll Back MLOps and deployment
- #208 Right to Erasure Across a Pipeline: Delete, Scan, Restore Data governance
- #238 Event-Driven AI Agents: Queues, Crashes and Duplicates Enterprise architecture and AI agents
