Highest-Spend Advertisement
Reported by candidates from Capital One's online assessment. Pattern, common pitfall, and the honest play if you blank under the timer.
The mistake that sinks a first attempt at this Capital One SQL question, reported in August 2026, is joining ads to statistics on only one video column and quietly dropping half the spend. Highest-Spend Advertisement asks for one ad_name, the one with the biggest total of watch_count * price_per_watch. Each ad has a long and a short version, and stats rows can point at junk platforms or videos that aren't ads at all. It's a join, filter, group, and top-one query. If you blank on the live OA, StealthCoder runs invisibly as a safety net and gives you the query.
The problem
An advertising company tracks advertisements, their long and short video versions, broadcasting platforms, and per-video broadcasting statistics. Find the advertisement on which the company spent the most money. For a statistics row, the broadcasting cost is watch_count * price_per_watch. Count a row only when its platform_id exists in platforms and its video_id is either the long or short version of an advertisement. Sum the qualifying costs across both versions and every recognized platform for each advertisement. Result Return exactly one row with the winning ad_name. Every testcase has a unique highest-spend advertisement. Tables ads: ad_id PK (Integer), ad_name (Text), long_version_video_id (Integer), short_version_video_id (Integer) videos: video_id PK (Integer), path (Text), duration (Integer) platforms: platform_id PK (Integer), contact_mail (Text), website (Text) ads_statistics: platform_id (Integer), video_id (Integer), watch_count (Integer), total_time_watched (Integer), price_per_watch (Decimal) Constraints Each input table contains at most 2000 rows. ad_id, ad_name, and every advertisement version ID are unique. Every long and short version ID exists in videos, and the two versions of an advertisement are distinct. watch_count and total_time_watched are non-negative signed 64-bit integers. price_per_watch is a non-negative exact decimal value. Statistics may name an unrecognized platform or a video that is not an advertisement version; those rows do not contribute. At least one advertisement has a qualifying statistics row, and the highest total spend is unique. Every per-row cost and per-advertisement total fits in DECIMAL(38, 12).
Reported by candidates. Source: FastPrep
Pattern and pitfall
The trick is matching each statistics row to an ad through either version. Join ads_statistics to ads on video_id = long_version_video_id OR video_id = short_version_video_id. Since the two versions of an ad are distinct, one stats row can match at most one ad, so no double counting. Then use an inner join to platforms on platform_id so unrecognized platforms drop out. Group by ad_id and ad_name, sum watch_count * price_per_watch, order by that sum descending, and limit 1. The pitfall is a LEFT JOIN to platforms, which keeps bad rows and inflates totals. Another is casting: watch_count is a 64-bit integer and price is an exact decimal, so don't use floats. Multiply as decimals. The highest total is unique, so you don't need tie handling. If the OR join feels shaky live, StealthCoder is the hedge that shows you the working query on screen.
StealthCoder is the hedge for the one pattern you didn't drill. It runs invisibly during the screen share.
You can drill Highest-Spend Advertisement 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. If you're reading this with an OA window open, you're who this was built for.
Get StealthCoderRelated leaked OAs
You've seen the question.
Make sure you actually pass Capital One's OA.
Capital One reuses patterns across OAs. If you're reading this with an OA window open, you're who this was built for. Works on HackerRank, CodeSignal, CoderPad, and Karat.
Highest-Spend Advertisement FAQ
What's the core trick in Highest-Spend Advertisement?+
Match stats rows to ads through either the long or short video ID, then sum cost per ad. Use an OR condition in the join, or a UNION ALL of two joins. Inner join platforms so unrecognized platform IDs are excluded. Then order by total descending and take one row.
Why do I need to join platforms at all?+
The problem says a row counts only if its platform_id exists in platforms. Statistics can reference platforms that aren't in the table. An inner join to platforms filters those out. Skip it and your totals include invalid rows, which can change the winning ad.
Should I worry about double counting with the OR join?+
No. The two versions of an ad are guaranteed distinct, and every version ID is unique across ads. A single statistics row's video_id can match only one ad, either as long or short. So the join never duplicates a row for the same ad.
How do I avoid precision problems on the cost?+
Keep everything as exact decimals. Multiply watch_count by price_per_watch directly in the SUM and don't cast to float or double. The constraints say totals fit in DECIMAL(38, 12), so the native decimal math is safe and exact.
How do I prep for this in 48 hours?+
Write the query twice from scratch. Practice joins with OR conditions, inner versus left join effects on filtering, GROUP BY with SUM of an expression, and ORDER BY with LIMIT 1. Then check edge cases: unknown platforms, non-ad videos, and ads with no stats rows.