Build Taxi Driver Performance Features
Reported by candidates from Capital One's online assessment. Pattern, common pitfall, and the honest play if you blank under the timer.
The mistake that sinks a first attempt on this Capital One SQL question is joining drivers to four ride tables separately and watching the counts multiply. It was reported in October 2026, and the task is to build one feature row per driver from drivers, cars, and rides_1 through rides_4. You need a UNION ALL of the partitions, a per-driver aggregation, and a LEFT JOIN so drivers with no rides survive with zeros. If you blank on the structure mid-assessment, StealthCoder is the hedge running invisibly on your screen. Know the shape before you sit down.
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
The trick is order of operations. Stack the four ride tables with UNION ALL first, not UNION, because dedup could drop legitimate rows. Aggregate that stacked set by driver_id in a CTE, then LEFT JOIN it onto drivers joined with cars. Wrap each aggregate in COALESCE(..., 0) so drivers with no rides return 0, not NULL. Count rejected rides with a conditional sum on status = 'Rejected by the driver', matching the exact string. Upvotes are the sum of four boolean columns cast to integers. Complaints and incidents are the same boolean sums. Date math is 2023-04-15 minus last_inspection_date in days, and experience is 2023 minus started_driving_year. The common pitfall is an inner join on rides, which silently deletes zero-ride drivers. Another is joining before aggregating, which inflates counts. StealthCoder is your safety net if the CTE layout slips during the live OA.
If you see this problem in your OA tomorrow, the play is to recognize the pattern in 30 seconds. StealthCoder buys you that recognition.
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. Built by an Amazon engineer who passed his OA cold and still thinks the filter is broken.
Get StealthCoderRelated leaked OAs
You've seen the question.
Make sure you actually pass Capital One's OA.
Capital One reuses patterns across OAs. Built by an Amazon engineer who passed his OA cold and still thinks the filter is broken. Works on HackerRank, CodeSignal, CoderPad, and Karat.
Build Taxi Driver Performance Features FAQ
What's the main trick in this Capital One SQL question?+
Combine the four ride tables with UNION ALL before any aggregation, group by driver_id, then LEFT JOIN that result to drivers and cars. That keeps zero-ride drivers and avoids multiplied counts from joining four tables separately.
How do I keep drivers who have no rides?+
Use a LEFT JOIN from drivers to your aggregated rides CTE, then wrap every ride-derived column in COALESCE(col, 0). Without that, those drivers show NULL instead of the required 0 for all four counts.
How do I count the boolean upvote columns?+
Cast each boolean to an integer and add them, or use CASE WHEN col THEN 1 ELSE 0 END. Sum that across the four upvote columns per driver. The same cast-and-sum approach works for complaints and incidents.
How should I compute days_since_inspection?+
Take the calendar-day difference between the fixed date 2023-04-15 and last_inspection_date. Use your dialect's date difference function and hardcode the date, not the current date. Experience is just 2023 minus started_driving_year.
How do I prepare for this in 48 hours?+
Write the full query once from scratch: UNION ALL CTE, grouped aggregates with conditional sums, LEFT JOIN, COALESCE. Then test it mentally on a driver with zero rides. That edge case is what this question is really checking.