Top Three Sales per County and Year
Clickhouse● MediumFULLTIMEPHONE SCREENProblem statement
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.
Table schema
MySQLPostgreSQLPandas
Use the same input data with any supported language. Open the Schema tab in the editor to see the generated SQL setup or Pandas DataFrames.
uk_price_paid
UK property sales used for the ranking query.
| Column | Type | Nullable | Description |
|---|---|---|---|
| date | Date | No | Property sale date. |
| county | Text | No | County containing the property. |
| price | Integer | No | Recorded sale price. |
Expected result
Your query or function must return these columns.
| Column | Type | Nullable | Description |
|---|---|---|---|
| county | Text | No | — |
| sale_year | Integer | No | — |
| price | Integer | No | — |
Row order: must match exactly. Numeric tolerance: 0.
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.