Build Taxi Driver Performance Features
Reported by candidates from Boston Consulting Group's online assessment. Pattern, common pitfall, and the honest play if you blank under the timer.
The Boston Consulting Group OA reported in October 2026 isn't a classic algorithm puzzle. It's a SQL feature-building task, and the whole thing hinges on one structure: a UNION ALL of the four ride partitions, aggregated per driver, then LEFT JOINed back to drivers and cars. Miss the left join and you drop drivers with no rides. Miss the union and you undercount. The query is long but every piece is simple. If you blank on the syntax mid-assessment, StealthCoder is the safety net running invisibly on your screen, but the plan below is enough to walk in with.
The problem
Build one performance-feature row for every driver using drivers, cars, and the four ride partitions rides_1 through rides_4. Use 2023-04-15 as today. Join each driver to the assigned car and return the result columns in the declared order. days_since_inspection is the calendar-day difference between 2023-04-15 and last_inspection_date. experience is 2023 - started_driving_year. number_of_rejected_rides counts rides whose status is exactly "Rejected by the driver". number_of_upvotes sums all true values across the four upvote columns. number_of_complaints and number_of_incidents count true values in their corresponding ride columns. Concatenate all four ride partitions before aggregation. Retain drivers with no rides and set all four ride-derived counts to 0. Tables drivers: driver_id PK (Integer), car_id (Integer), age (Integer), started_driving_year (Integer), second_language (Text), rating (Decimal), net_worth_of_tips (Decimal), driver_class (Text) cars: car_id PK (Integer), model (Text), manufacture_year (Integer), last_inspection_date (Date) rides_1: ride_id PK (Integer), driver_id (Integer), date (Date), status (Text), car_clearness_upvote_given (Boolean), politeness_upvote_given (Boolean), communication_upvote_given (Boolean), punctuality_upvote_given (Boolean), complaint_given (Boolean), incident_occurred (Boolean) rides_2: ride_id PK (Integer), driver_id (Integer), date (Date), status (Text), car_clearness_upvote_given (Boolean), politeness_upvote_given (Boolean), communication_upvote_given (Boolean), punctuality_upvote_given (Boolean), complaint_given (Boolean), incident_occurred (Boolean) rides_3: ride_id PK (Integer), driver_id (Integer), date (Date), status (Text), car_clearness_upvote_given (Boolean), politeness_upvote_given (Boolean), communication_upvote_given (Boolean), punctuality_upvote_given (Boolean), complaint_given (Boolean), incident_occurred (Boolean) rides_4: ride_id PK (Integer), driver_id (Integer), date (Date), status (Text), car_clearness_upvote_given (Boolean), politeness_upvote_given (Boolean), communication_upvote_given (Boolean), punctuality_upvote_given (Boolean), complaint_given (Boolean), incident_occurred (Boolean) Constraints Every driver references exactly one row in cars. Every ride references a row in drivers. Every date is a valid ISO calendar date no later than 2023-04-15. started_driving_year is no later than 2023. age may be null; all other driver and car fields are non-null. Result row order is ignored.
Reported by candidates. Source: FastPrep
Pattern and pitfall
Step one: stack rides_1 through rides_4 with UNION ALL, not UNION. UNION dedupes rows and can silently kill legitimate ones. Put that in a CTE. Step two: aggregate by driver_id with conditional sums. Rejected rides is SUM(CASE WHEN status = 'Rejected by the driver' THEN 1 ELSE 0 END), matched exactly. Upvotes is the sum of four boolean columns, each cast to 0 or 1 through CASE. Complaints and incidents work the same way. Step three: start from drivers, JOIN cars on car_id, then LEFT JOIN the aggregate and wrap each count in COALESCE(..., 0). That handles drivers with no rides. Step four: days_since_inspection is the date difference between 2023-04-15 and last_inspection_date, and experience is 2023 - started_driving_year. Hardcode the date, don't use the current date. The common pitfall is inner joining the aggregate. The second is returning columns out of the declared order. Date diff syntax varies by engine, so check which one you're on.
StealthCoder is the hedge for the one pattern you didn't drill. It runs invisibly during the screen share.
You can drill Build Taxi Driver Performance Features cold, or you can hedge it. StealthCoder runs invisibly during screen share and surfaces a working solution in under 2 seconds. The proctor sees the IDE. They don't see what's behind it. If you're reading this with an OA window open, you're who this was built for.
Get StealthCoderRelated leaked OAs
You've seen the question.
Make sure you actually pass Boston Consulting Group's OA.
Boston Consulting Group reuses patterns across OAs. If you're reading this with an OA window open, you're who this was built for. Works on HackerRank, CodeSignal, CoderPad, and Karat.
Build Taxi Driver Performance Features FAQ
What's the trick in the Boston Consulting Group driver features query?+
Combine the four ride tables with UNION ALL in a CTE, aggregate per driver with conditional sums, then LEFT JOIN that onto drivers and cars. COALESCE every ride-derived count to 0 so drivers with no rides stay in the result.
Why UNION ALL and not UNION?+
UNION removes duplicate rows, which could drop real rides that look identical across columns. UNION ALL keeps every row, and it's faster. You want every ride counted exactly once, so ALL is the right call.
How do I sum the four upvote booleans?+
Convert each boolean to 1 or 0 with CASE WHEN col THEN 1 ELSE 0 END, then add the four per ride and sum per driver. Some engines let you cast booleans to integers directly, but CASE works everywhere and is safer.
How do I handle drivers with zero rides?+
Start from drivers and LEFT JOIN the ride aggregate. The ride columns come back NULL for those drivers, so wrap number_of_rejected_rides, upvotes, complaints and incidents in COALESCE(x, 0). An inner join would silently remove them.
How do I prepare for this in 48 hours?+
Practice one query that mixes a UNION ALL CTE, conditional aggregation, a LEFT JOIN and COALESCE. Then check your engine's date difference function, since 2023-04-15 must be hardcoded. Verify the output column order matches the spec before submitting.