Predict House Prices
Reported by candidates from Capital One's online assessment. Pattern, common pitfall, and the honest play if you blank under the timer.
The brute-force worry on this Capital One question, reported September 2026, isn't row count. It's that you're asked to do k-nearest-neighbors regression inside a SQL query. Standardize features, find the 3 closest labeled rows per test row, average log(1 + price), invert, round. If your OA lands this week, expect a CROSS JOIN between test and labeled rows, then a window function to rank by distance. It's a long query, not a hard idea. If you blank on the structure mid-assessment, StealthCoder is the invisible safety net that reads the problem and hands you the query.
The problem
You are given preprocessed train_data, validation_data, and test_data. Use the labeled training and validation rows to predict a price for every test row. For this deterministic practice version: Do not use house_id or price as model features. Combine the training and validation rows. Standardize each feature with that combined labeled mean and population standard deviation; ignore features whose labeled standard deviation is 0. For each test row, select the three nearest labeled rows by squared Euclidean distance, or all labeled rows when fewer than three exist. Break equal-distance ties by labeled input order. Transform neighbor prices with log(1 + price), average those transformed values, apply the inverse transform, and round the predicted price to six decimal places. Return exactly one column named price, preserving the original test_data row order. Tables train_data: house_id PK (Integer), bedrooms (Decimal), bathrooms (Decimal), sqft_living (Decimal), sqft_lot (Decimal), floors (Decimal), waterfront (Integer), age (Decimal), num_views (Decimal), condition (Integer), price (Decimal) validation_data: house_id PK (Integer), bedrooms (Decimal), bathrooms (Decimal), sqft_living (Decimal), sqft_lot (Decimal), floors (Decimal), waterfront (Integer), age (Decimal), num_views (Decimal), condition (Integer), price (Decimal) test_data: house_id PK (Integer), bedrooms (Decimal), bathrooms (Decimal), sqft_living (Decimal), sqft_lot (Decimal), floors (Decimal), waterfront (Integer), age (Decimal), num_views (Decimal), condition (Integer), price (Decimal) Constraints Every input table is non-empty. Training and validation prices are non-null and nonnegative. Test prices are null and must not be used. Every feature value is finite. All three tables have the same feature columns. The output has exactly one row per test row.
Reported by candidates. Source: FastPrep
Pattern and pitfall
The trick is a pipeline of CTEs. First, UNION ALL train and validation with an order column so ties break by labeled input order (train first, then validation, each in its own row order). Second, compute AVG and population stddev per feature (STDDEV_POP), and skip any feature where it equals 0. Third, CROSS JOIN test rows to labeled rows and sum squared standardized differences. Fourth, ROW_NUMBER() OVER (PARTITION BY test house_id ORDER BY dist, labeled_order) and keep rn <= 3. Fifth, AVG(LN(1 + price)), then EXP(avg) - 1, ROUND to 6. The pitfalls: using sample stddev instead of population, dividing by zero on constant features, losing the test row order, and using RANK instead of ROW_NUMBER, which breaks ties. Order the final output by the original test position. If the order logic trips you up live, StealthCoder is your hedge. Don't select the price column from test data.
If this hits your live OA and you blank, StealthCoder solves it in seconds, invisible to the proctor.
You can drill Predict House Prices 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 would have shipped this the night before his JPMorgan OA if he'd had it.
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 would have shipped this the night before his JPMorgan OA if he'd had it. Works on HackerRank, CodeSignal, CoderPad, and Karat.
Predict House Prices FAQ
How hard is the Capital One Predict House Prices SQL question really?+
The logic is easy, the volume of SQL is not. It's about six CTEs: union, stats, standardize, distance, rank, aggregate. Each step is simple. The risk is typos and subtle rules like population stddev and tie-breaking. Write it in stages and check each CTE.
What's the trick to finding the 3 nearest neighbors in SQL?+
CROSS JOIN each test row with every labeled row, compute squared distance, then use ROW_NUMBER() partitioned by test house_id and ordered by distance, then labeled input order. Filter to rn <= 3. ROW_NUMBER guarantees exactly three rows even with tied distances.
How do I handle features with zero standard deviation?+
Ignore them. Compute STDDEV_POP per feature over the combined train and validation rows. If it's 0, drop that feature from the distance sum, or use CASE to contribute 0. Dividing by zero would break the whole query, so guard every feature column.
How do I preserve the test row order and return only price?+
Carry a test row position through every CTE, using ROW_NUMBER() over test_data in its original order, or its input order if house_id isn't sorted. Order the final SELECT by that position and output only the rounded price column, aliased as price.
How do I apply the log transform and invert it?+
Average LN(1 + price) over the three neighbors, then compute EXP(avg_log) - 1 and wrap it in ROUND(..., 6). Don't average raw prices. Prices are nonnegative, so 1 + price is always at least 1 and the log is safe.