Summarize Taxi Drivers and Rides
Reported by candidates from Capital One's online assessment. Pattern, common pitfall, and the honest play if you blank under the timer.
Capital One reported this SQL question in October 2026, and it looks easy until one detail bites you. You get a drivers table and four ride partitions, and you need a single row with three numbers. It's aggregation plus UNION ALL, nothing exotic. The trap is in the wording: "not exactly no" and "exactly Success" are string comparisons, and a sloppy query returns a plausible wrong answer. If you blank on the live OA, StealthCoder sits invisibly on your screen as a safety net. Know the shape first.
The problem
You are given one drivers table and four ride partitions named rides_1 through rides_4. Return a one-row summary with exactly these columns: average_rating: the arithmetic mean of rating across all drivers. second_language_ratio: the number of drivers whose second_language is not exactly "no", divided by the total number of drivers. successful_ride_ratio: the number of rides whose status is exactly "Success", divided by the number of rides across all four partitions. Return the columns in the listed order. Tables drivers: driver_id PK (Integer), age (Integer), second_language (Text), rating (Decimal) rides_1: ride_id PK (Integer), driver_id (Integer), status (Text) rides_2: ride_id PK (Integer), driver_id (Integer), status (Text) rides_3: ride_id PK (Integer), driver_id (Integer), status (Text) rides_4: ride_id PK (Integer), driver_id (Integer), status (Text) Constraints drivers contains at least one row. At least one ride exists across rides_1, rides_2, rides_3, and rides_4. Every ride references a driver present in drivers. Each rating is finite. Ratios are values between 0 and 1, inclusive.
Reported by candidates. Source: FastPrep
Pattern and pitfall
The trick is to keep each ratio on its own denominator and never join drivers to rides. Build the ride total with UNION ALL across rides_1 to rides_4. Use UNION and you'll silently drop duplicate rows, though ride_id is a primary key per table so overlap across partitions is the real risk. UNION ALL counts every ride. Then compute SUM(CASE WHEN status = 'Success' THEN 1 ELSE 0 END) divided by COUNT(*). For drivers, use AVG(rating) and the share where second_language <> 'no'. The edge case: integer division. Cast to decimal or multiply by 1.0 first, or you get 0. Also watch NULL second_language, since NULL <> 'no' evaluates to unknown and gets skipped. Combine the two subqueries with a CROSS JOIN to return one row. If the syntax slips under pressure, StealthCoder can supply the full query live.
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 Summarize Taxi Drivers and Rides 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.
Summarize Taxi Drivers and Rides FAQ
How hard is the Capital One Summarize Taxi Drivers and Rides question really?+
Easy to medium. No window functions or tricky joins. The difficulty is precision: exact string matches, integer division, and combining four tables correctly. If you've written a UNION ALL and a CASE aggregate before, you can finish it quickly.
What's the main trick in this query?+
Compute the driver stats and ride stats separately, then CROSS JOIN the two one-row results. Joining drivers to rides would weight each driver by ride count and corrupt the average rating and the language ratio.
Why UNION ALL instead of UNION?+
UNION removes duplicate rows, which can change your ride count and the success ratio. UNION ALL keeps every row from all four partitions, which is what the question asks for. It's also faster since it skips the dedupe step.
How do I avoid the integer division bug?+
Multiply the numerator by 1.0 or cast it to a decimal before dividing. In many SQL engines, COUNT divided by COUNT returns 0 or 1 as an integer. Check your output is a fraction between 0 and 1.
How do I prepare for this in 48 hours?+
Practice one multi-table aggregate: UNION ALL of partitions, conditional counting with CASE, and a CROSS JOIN of single-row subqueries. Test on small data with a NULL and a mixed-case status value so you see how exact comparisons behave.