Prepare Taxi Driver Classification Data
Reported by candidates from Capital One's online assessment. Pattern, common pitfall, and the honest play if you blank under the timer.
This Capital One OA from October 2026 looks like a machine learning question, but it's really a SQL preprocessing job. You're fitting a scaler and two label encoders on train, then applying them to both tables with no leakage. If you've got the OA in a day or two, expect the fiddly details to hurt you, not the logic. StealthCoder is the safety net if you blank on the dense-rank or window syntax mid-assessment, running invisibly while you work the query.
The problem
Prepare train_drivers and test_drivers for a binary driver-classification task. Return both transformed datasets in one table, with all training rows first and all test rows second. Add dataset as the first column, using "train" or "test". Compute the mean non-null training age. Fill missing ages in both datasets with that mean, then truncate every age to an integer. For car_model and second_language, sort the distinct training strings in ascending order and encode them as 0, 1, and so on. Encode any value not seen in training as -1. Compute the training mean and population standard deviation of net_worth_of_tips. Standardize that column in both datasets with those training statistics and round it to five decimal places. If the training standard deviation is 0, return 0 for every standardized tip value. Encode "A class" as 0 and "B class" as 1. Preserve a null test label as null. Keep all other values unchanged and return the declared columns in order. Tables train_drivers: driver_id PK (Integer), car_model (Text), car_manufacture_year (Integer), days_since_inspection (Integer), age (Integer), experience (Integer), second_language (Text), rating (Decimal), net_worth_of_tips (Decimal), number_of_rejected_rides (Integer), number_of_upvotes (Integer), number_of_complaints (Integer), number_of_incidents (Integer), driver_class (Text) test_drivers: driver_id PK (Integer), car_model (Text), car_manufacture_year (Integer), days_since_inspection (Integer), age (Integer), experience (Integer), second_language (Text), rating (Decimal), net_worth_of_tips (Decimal), number_of_rejected_rides (Integer), number_of_upvotes (Integer), number_of_complaints (Integer), number_of_incidents (Integer), driver_class (Text) Constraints train_drivers and test_drivers are both non-empty. At least one training age is non-null. Training rows have driver_class equal to "A class" or "B class". Test labels may be null. Every numeric input is finite. Result row order is exact.
Reported by candidates. Source: FastPrep
Pattern and pitfall
The trick is computing statistics from train_drivers only, then cross-joining them onto a UNION ALL of both tables. Build CTEs: one for AVG(age) over non-null training ages, one for AVG and population STDDEV of net_worth_of_tips, and two encoding maps using DENSE_RANK() or ROW_NUMBER() minus 1 over DISTINCT training strings, ordered ascending. LEFT JOIN the maps and COALESCE to -1 for unseen values. Truncate the filled age with CAST to integer or TRUNC, not ROUND. Pitfalls: using sample stddev instead of population, computing stats after the union so test rows leak in, forgetting the zero stddev case (use CASE), and losing row order. Add a sort key column (0 for train, 1 for test) and ORDER BY it, then driver_id. Keep null test labels null with a CASE that has no ELSE default. If the syntax slips under pressure, StealthCoder can cover the live OA.
Drill it cold or hedge it with StealthCoder. Either way, don't walk into the OA hoping you remember the trick.
You can drill Prepare Taxi Driver Classification Data 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. Made for the candidate who got the OA invite this morning and has 72 hours, not six months.
Get StealthCoderRelated leaked OAs
You've seen the question.
Make sure you actually pass Capital One's OA.
Capital One reuses patterns across OAs. Made for the candidate who got the OA invite this morning and has 72 hours, not six months. Works on HackerRank, CodeSignal, CoderPad, and Karat.
Prepare Taxi Driver Classification Data FAQ
How hard is this Capital One SQL question really?+
The logic is easy, the volume of details is the problem. You need train-only statistics, two encoders, null handling, rounding, and exact row order. Each piece is a basic CTE or join. Most failures come from small misses, not hard concepts.
What's the main trick?+
Compute every statistic from train_drivers alone, then apply it to both tables via a UNION ALL joined to those one-row stat CTEs. That avoids leakage and keeps the query readable. Build the encoding maps separately and LEFT JOIN them.
How do I encode categories in SQL?+
Select DISTINCT training values, then use DENSE_RANK() OVER (ORDER BY value) minus 1 to get 0, 1, 2 and so on. LEFT JOIN each dataset on the string and COALESCE the result to -1 for values never seen in training.
What edge cases should I check?+
Zero training standard deviation must return 0 for every tip. Null ages need the training mean before truncation. Null test labels stay null. Use population standard deviation, round tips to five decimals, and truncate age rather than rounding it.
How do I keep the row order exact?+
Add a hidden ordering column, 0 for train and 1 for test, in the unioned CTE. Then ORDER BY that column and driver_id in the final select. Don't rely on UNION ALL's natural order, since databases don't guarantee it.