Analyze House and Viewing Metrics
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 SQL question, reported in September 2026, looks like three separate queries but it's really one UNION ALL and three scalar aggregates stacked into a fixed three-row result. If you have the OA in the next day or two, the work is mostly plumbing: merge four partitions, compute a mean, a percentage, and a filtered average of per-house counts. Row order is exact, so the stacking order matters. StealthCoder sits invisibly on your screen as a safety net if you blank on the structure mid-assessment, but the shape here is simple enough to hold in your head.
The problem
Combine the four house partitions houses_0 through houses_3 and analyze them together with house_views. Return exactly three rows with columns insight_type and value, in this order: average_house_price: the arithmetic mean of price across every house. percentage_waterfront_houses: the percentage of houses whose waterfront value is 1, on the 0-to-100 scale. average_views_per_viewed_house_last_30_days: use view dates from 2023-03-16 through 2023-04-15, inclusive; count views per house, then average those counts across houses with at least one qualifying view. Return 0 when no house has a qualifying view. Tables houses_0: house_id PK (Integer), bedrooms (Integer), bathrooms (Decimal), sqft_living (Integer), sqft_lot (Integer), floors (Decimal), waterfront (Integer), age (Integer), price (Decimal) houses_1: house_id PK (Integer), bedrooms (Integer), bathrooms (Decimal), sqft_living (Integer), sqft_lot (Integer), floors (Decimal), waterfront (Integer), age (Integer), price (Decimal) houses_2: house_id PK (Integer), bedrooms (Integer), bathrooms (Decimal), sqft_living (Integer), sqft_lot (Integer), floors (Decimal), waterfront (Integer), age (Integer), price (Decimal) houses_3: house_id PK (Integer), bedrooms (Integer), bathrooms (Decimal), sqft_living (Integer), sqft_lot (Integer), floors (Decimal), waterfront (Integer), age (Integer), price (Decimal) house_views: viewer_id (Integer), house_id (Integer), view_date (Date) Constraints At least one house exists across the four partitions. house_id values are unique across all four house partitions. waterfront is 0 or 1. Every price is finite and nonnegative. Every view_date is a valid ISO calendar date. Result row order is exact.
Reported by candidates. Source: FastPrep
Pattern and pitfall
The trick is a CTE that UNION ALLs houses_0 through houses_3 into one set, then three single-row selects stacked with UNION ALL in the required order. Average price is AVG(price). Waterfront percentage is 100.0 * SUM(waterfront) / COUNT(*), and the 100.0 matters because integer division will hand you 0. The third metric is a two-step aggregate. First filter house_views to 2023-03-16 through 2023-04-15 inclusive, group by house_id to get counts, then AVG those counts. Wrap it in COALESCE(..., 0) because an empty set returns NULL, and the spec wants 0. Common pitfalls: using UNION instead of UNION ALL (harmless here since house_id is unique, but wasteful), averaging raw view rows instead of per-house counts, and a BETWEEN that misses the end date if view_date were a timestamp. It's a Date, so BETWEEN is fine. Keep the literal insight_type strings exact. If you freeze, StealthCoder is the hedge on the live OA.
If you see this problem in your OA tomorrow, the play is to recognize the pattern in 30 seconds. StealthCoder buys you that recognition.
You can drill Analyze House and Viewing Metrics 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 passed his OA cold and still thinks the filter is broken.
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 passed his OA cold and still thinks the filter is broken. Works on HackerRank, CodeSignal, CoderPad, and Karat.
Analyze House and Viewing Metrics FAQ
What's the trick in the Capital One house metrics SQL question?+
Stack three scalar results with UNION ALL over a CTE that merges the four house tables. The only real subtlety is the third metric, which averages per-house view counts, not raw rows. Group by house_id first, then average the counts in an outer query.
Why does my waterfront percentage come back as 0?+
Integer division. SUM(waterfront) divided by COUNT(*) on integers truncates. Multiply by 100.0 first, or cast to decimal, so you get something like 100.0 * SUM(waterfront) / COUNT(*). Waterfront is only 0 or 1, so the sum is the count of waterfront houses.
How do I handle the case where no house has a qualifying view?+
AVG over an empty set returns NULL, but the spec says return 0. Wrap the outer average in COALESCE(AVG(view_count), 0). Because the average is a scalar aggregate, it still returns one row, so your three-row output stays intact.
Does the order of the three rows matter?+
Yes, the problem says result row order is exact. Order is average_house_price, percentage_waterfront_houses, then average_views_per_viewed_house_last_30_days. UNION ALL generally preserves order in practice, but to be safe add a sort key column in a subquery and order by it.
How do I prepare for this in 48 hours?+
Practice three things: UNION ALL across identical-schema tables, conditional aggregation for percentages, and a two-level aggregate (GROUP BY then AVG). Write this exact query once from scratch with a CTE. Check the date range is inclusive on both ends and that insight_type labels match the spec.