Prepare Taxi Driver Classification Data
Reported by candidates from Boston Consulting Group'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 Boston Consulting Group SQL question is computing any statistic from the combined data. Reported in October 2026, "Prepare Taxi Driver Classification Data" is a feature-prep task in query form. You build one table with train rows first, test rows second, and every transform fitted on training only. If you've got an OA invite, expect CTEs, a UNION ALL, and a few traps around NULLs and unseen values. StealthCoder is the safety net if you blank mid-assessment, but the logic below is small enough to hold in your head.
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 fitting on train and applying to both. Build a stats CTE from train_drivers: AVG(age) ignores NULLs, AVG(net_worth_of_tips), and the population standard deviation (STDDEV_POP, or sqrt of avg of squares minus square of avg). Build two mapping CTEs using DENSE_RANK() minus 1 over distinct training car_model and second_language, ordered ascending. LEFT JOIN the mappings onto both tables and COALESCE to -1 for unseen values. Fill age with COALESCE(age, mean), then cast to integer. Since the mean is positive, truncation and flooring match, but use an explicit truncation anyway. Standardize with a CASE: if std is 0, return 0, else ROUND((tip - mean) / std, 5). Pitfalls: computing stats over the union, forgetting that a NULL test label must stay NULL, and losing row order. Add a sort key column (0 for train, 1 for test) and ORDER BY it. StealthCoder can cover you live if the window or join syntax slips, but the structure is the whole problem.
Memorize the pattern. If you can't, run StealthCoder. The proctor sees the IDE. They don't see what's behind it.
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 by an engineer who treats the OA as theater. If yours is tonight, you don't have time to grind. You have time to hedge.
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. Made by an engineer who treats the OA as theater. If yours is tonight, you don't have time to grind. You have time to hedge. Works on HackerRank, CodeSignal, CoderPad, and Karat.
Prepare Taxi Driver Classification Data FAQ
What's the main trick in the Boston Consulting Group taxi driver data prep question?+
Fit everything on training data only. Mean age, tip mean, tip standard deviation, and the category mappings all come from train_drivers. Then apply those same values to both tables. Computing stats on the union is the classic mistake and it changes every standardized value.
How do I encode car_model and second_language in SQL?+
Take the distinct non-null training values, assign DENSE_RANK() over the string ascending, and subtract 1 so it starts at 0. LEFT JOIN that mapping to each table and wrap the result in COALESCE(code, -1) so values unseen in training get -1.
How do I keep train rows first and test rows second?+
UNION ALL doesn't guarantee order on its own. Add a literal sort column, 0 for train and 1 for test, and ORDER BY it. Add driver_id as a secondary sort inside each group, since the result order is stated as exact. Don't return the helper column.
What about the zero standard deviation and NULL edge cases?+
Use a CASE on the training standard deviation. If it equals 0, output 0 for every standardized tip, otherwise divide. For labels, map 'A class' to 0 and 'B class' to 1, and let anything else, including a NULL test label, fall through to NULL.
How do I prepare for this in 48 hours?+
Write the query once end to end on a small made-up table. Practice CTEs, DENSE_RANK, LEFT JOIN with COALESCE, population standard deviation, and UNION ALL with an ordering column. Check the ROUND to five decimals and the integer truncation of age. That covers every moving part.