Build House Viewing Features
Reported by candidates from Capital One's online assessment. Pattern, common pitfall, and the honest play if you blank under the timer.
The edge case that breaks a naive solution here is the houses with zero views, and Capital One's September 2026 SQL question is built around it. You're merging four house tables with different column spellings, then attaching a view count for a 31-day window. Inner join the views and every unviewed house vanishes from your output. If the OA lands in your inbox this week, this is a normalize, UNION ALL, LEFT JOIN problem. StealthCoder sits invisible on your screen as a safety net if you blank mid-assessment, but the pattern is simple once you see it.
The problem
Normalize the reported column-name variants in houses_0 through houses_3, combine the four house partitions, and add recent viewing activity. Use view dates from 2023-03-16 through 2023-04-15, inclusive. Count qualifying rows in house_views for each house_id. Add the count as num_views; houses with no qualifying view receive 0. Return the declared canonical columns, sorted by house_id ascending. Tables houses_0: house_id (Integer), bedrooms (Integer), bathrooms (Decimal), sqft_living (Integer), sqft_lot (Integer), floors (Decimal), waterfront (Integer), age (Integer), price (Decimal) houses_1: houseid (Integer), bed_rooms (Integer), bath_rooms (Decimal), sqftliving (Integer), sqft_lot (Integer), floors (Decimal), water_front (Integer), age (Integer), price (Decimal) houses_2: house_id (Integer), bedrooms (Integer), bathrooms (Decimal), sqft_living (Integer), sqftlot (Integer), floor (Decimal), waterfront (Integer), house_age (Integer), price (Decimal) houses_3: house_id (Integer), bedrooms (Integer), bathrooms (Decimal), living_sqft (Integer), lot_sqft (Integer), floors (Decimal), waterfront (Integer), age (Integer), house_price (Decimal) house_views: viewer_id (Integer), house_id (Integer), view_date (Date) Constraints house_id values are unique across all four house partitions. Every house_views.house_id identifies a supplied house. Every view_date is a valid ISO calendar date. The listed source-column variants are the only spellings that require normalization. Result row order is exact.
Reported by candidates. Source: FastPrep
Pattern and pitfall
The trick is three steps. First, alias each partition's columns to one canonical set: houseid to house_id, bed_rooms to bedrooms, sqftliving to sqft_living, floor to floors, house_age to age, house_price to price, and so on. Second, UNION ALL the four selects, which is safe since house_id is unique across partitions. Third, aggregate house_views separately, filtering view_date BETWEEN '2023-03-16' AND '2023-04-15', grouped by house_id. Then LEFT JOIN that to the combined houses and wrap the count in COALESCE(..., 0). The pitfall is putting the date filter in a WHERE clause after the join, which silently drops zero-view houses. Filter inside the subquery or in the ON clause. Also double-check each column's order across the four SELECTs, since UNION ALL matches by position, not name. Finish with ORDER BY house_id ASC. If you freeze on the aliasing, StealthCoder can supply the query live.
If this hits your live OA and you blank, StealthCoder solves it in seconds, invisible to the proctor.
You can drill Build House Viewing Features 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.
Build House Viewing Features FAQ
What's the trick in the Capital One house viewing SQL question?+
Keep houses with no views. Aggregate house_views in a filtered subquery grouped by house_id, then LEFT JOIN it onto the combined houses table and use COALESCE(num_views, 0). An inner join or a post-join WHERE filter drops the zero-view houses and fails the tests.
How do I combine the four houses tables with different column names?+
Write four SELECTs that alias every column to the canonical name, in the same order, then UNION ALL them. For example, houseid AS house_id, bed_rooms AS bedrooms, floor AS floors, house_price AS price. UNION ALL is fine since house_id is unique across partitions.
Is the date range inclusive, and how do I filter it?+
Yes, both ends are inclusive. Use view_date BETWEEN '2023-03-16' AND '2023-04-15'. Since view_date is a Date type, no time-of-day issue applies. Put this filter in the views subquery, not in a WHERE after the LEFT JOIN.
Does the output order matter?+
Yes, the problem says row order is exact. End with ORDER BY house_id ASC. Without it, the UNION ALL and join may return rows in any order, and a correct query could still fail the checker.
How do I prepare for this in 48 hours?+
Practice one LEFT JOIN with a pre-aggregated subquery and COALESCE until it's automatic. Then write a UNION ALL with aliased columns from memory. Those two skills cover this whole question. Don't bother with window functions, they aren't needed here.