Top Three Sales per County and Year
Reported by candidates from Clickhouse's online assessment. Pattern, common pitfall, and the honest play if you blank under the timer.
The Clickhouse OA reported in July 2026 hides its trap in the ties. Top three per county and year looks like a quick GROUP BY, and then the naive version quietly returns the wrong row count. This is a SQL question on the uk_price_paid table, and the pattern is a window function with a partition. You filter to 2020 through 2022, rank inside each county-year group, keep three, and sort. If you blank on the ranking function choice, StealthCoder runs invisibly during the live OA as a safety net. Most of this is knowing which function to pick.
The problem
The uk_price_paid table contains UK property sales with a sale date, county, and price. For every county and calendar year from 2020 through 2022, return the three most expensive sales. Rank sales independently inside each county-year group. Return county, sale_year, and price. Order the result by county ascending, sale_year ascending, and price descending. Tables uk_price_paid: date (Date), county (Text), price (Integer) Constraints Each row has a non-null date, county, and positive integer price. Every county-year group represented from 2020 through 2022 contains at least three rows. Rows outside 2020 through 2022 are ignored. If prices tie, any three tied source rows may be selected; because the result contains only county, year, and price, tied rows are indistinguishable. Each testcase contains at most 2,000 rows.
Reported by candidates. Source: FastPrep
Pattern and pitfall
The trick is ROW_NUMBER() OVER (PARTITION BY county, EXTRACT(YEAR FROM date) ORDER BY price DESC) in a subquery or CTE, then WHERE rn <= 3. Filter the date range first, either inside the CTE or in the WHERE clause. The pitfall is RANK or DENSE_RANK. With tied prices they can return four or more rows for one group, and the spec says exactly three rows are expected, with tied rows indistinguishable. ROW_NUMBER guarantees three. Another pitfall is grouping by date instead of year, or using LIMIT 3 globally, which only returns three rows total. Also, you can't filter on a window function in the same SELECT's WHERE, so wrap it. Finish with ORDER BY county, sale_year, price DESC. Clickhouse uses toYear(date), but standard year extraction works in most dialects. If the syntax slips under pressure, StealthCoder can supply the query live as a hedge.
If this hits your live OA and you blank, StealthCoder solves it in seconds, invisible to the proctor.
You can drill Top Three Sales per County and Year 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 StealthCoderYou've seen the question.
Make sure you actually pass Clickhouse's OA.
Clickhouse 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.
Top Three Sales per County and Year FAQ
What's the trick in the Clickhouse top three sales question?+
Use ROW_NUMBER partitioned by county and year, ordered by price descending, then keep rn <= 3 in an outer query. ROW_NUMBER matters because it returns exactly three rows per group even when prices tie.
Why not use RANK or DENSE_RANK?+
Both give tied rows the same rank, so a tie at third place can return more than three rows per group. The problem says tied rows are indistinguishable, so ROW_NUMBER is safe and gives a fixed count.
How do I get the year from the date column?+
Use EXTRACT(YEAR FROM date) or toYear(date) in Clickhouse. Alias it as sale_year in the inner query so the outer SELECT and ORDER BY can reuse it. Filter to 2020 through 2022 before ranking.
Can I put the rank filter in the WHERE clause directly?+
No. Window functions run after WHERE, so you need a subquery or CTE that computes the rank, then filter on it outside. Forgetting this is the most common syntax error on this type of question.
How do I prep for this in 48 hours?+
Write the partition plus ROW_NUMBER pattern from memory three times on different tables. Then practice the ordering clause and the CTE wrapper. That covers nearly every top N per group question you'd see on a SQL OA.