Prepare House Price Regression Data
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 OA in September 2026, and it's not a puzzle so much as a data-prep chore dressed up as one. You get train_houses and test_houses, and you have to output one table with train rows first, then test rows. The catch is that every statistic comes from training data only. If you've got the invite for tomorrow, this is about CTEs, UNION ALL, and scalar stats applied across both sets. StealthCoder is the quiet backup on the live OA if the standardization math freezes you, but the plan below is short enough to hold in your head.
The problem
Prepare train_houses and test_houses for house-price regression. 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 of the non-null training age values, round it down to an integer, and fill missing ages in both datasets with that value. Map "Poor", "Fair", "Good", and "Excellent" to 0, 1, 2, and 3. Using training rows only, standardize bedrooms, bathrooms, sqft_living, sqft_lot, floors, age, and num_views with the population standard deviation. Round standardized values to five decimal places; when a training standard deviation is 0, return 0 for that column in both datasets. Keep house_id, waterfront, and price unchanged. Preserve a null test price as null. Tables train_houses: house_id PK (Integer), bedrooms (Integer), bathrooms (Integer), sqft_living (Integer), sqft_lot (Integer), floors (Decimal), waterfront (Integer), age (Integer), num_views (Integer), condition_name (Text), price (Decimal) test_houses: house_id PK (Integer), bedrooms (Integer), bathrooms (Integer), sqft_living (Integer), sqft_lot (Integer), floors (Decimal), waterfront (Integer), age (Integer), num_views (Integer), condition_name (Text), price (Decimal) Constraints Both input tables are non-empty. At least one training age is non-null. Every condition_name is "Poor", "Fair", "Good", or "Excellent". Training prices are non-null; test prices may be null. Every numeric input is finite. Result row order is exact.
Reported by candidates. Source: FastPrep
Pattern and pitfall
The input size isn't the issue here. The trap is correctness: brute force means a correlated subquery per row per column, which is slow and ugly. Instead, build one CTE that computes training stats once: FLOOR(AVG(age)) for the fill value, then the mean and population standard deviation (STDDEV_POP) for the seven columns, computed after the age fill so age is standardized on the filled values. Then UNION ALL the two tables with a literal dataset column and a sort key (0 for train, 1 for test), CROSS JOIN the one-row stats CTE, and apply COALESCE(age, fill), a CASE for condition_name, and ROUND((x - mean) / NULLIF(std, 0), 5). Wrap that in COALESCE(..., 0) so zero deviation returns 0. Pitfalls: using sample stddev, taking test stats, and forgetting ORDER BY on the sort key and house_id. StealthCoder is the hedge if you blank on the live OA, but this structure is the whole trick.
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 House Price Regression 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 Capital One's OA.
Capital One 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 House Price Regression Data FAQ
What's the trick in this Capital One SQL question?+
Compute training statistics once in a CTE, then CROSS JOIN that one-row result onto both datasets. Never recompute stats from test rows. The age fill value is FLOOR of the training mean, and the standardization of age uses the filled values, not the raw ones.
Should I use population or sample standard deviation?+
Population, as the prompt says. Use STDDEV_POP or compute SQRT(AVG(x*x) - AVG(x)*AVG(x)) if your dialect lacks it. Sample stddev divides by n-1 and will give slightly different values that fail the five-decimal check.
How do I handle a zero standard deviation?+
Divide by NULLIF(std, 0), which returns NULL, then wrap the whole expression in COALESCE(..., 0). That returns 0 for that column in both train and test. Do the check per column, since one column can be constant while others aren't.
How do I guarantee train rows come first?+
UNION ALL gives no ordering guarantee on its own. Add a sort column, 0 for train and 1 for test, in each branch, then ORDER BY it and house_id in the outer query. Don't include that helper column in the final SELECT.
How do I prepare for this in 48 hours?+
Practice one pattern: CTE for stats, UNION ALL for combining, CASE for mapping, ROUND and COALESCE for cleanup. Write it once against a toy table. Watch NULL behavior: AVG ignores nulls, and the test price must stay null, so don't coalesce price.