Build House Viewing Features
Capital One● MediumFULLTIMEOAProblem statement
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.
Table schema
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.
houses_0
House partition 0 with its source-visible column spellings.
| Column | Type | Nullable | Description |
|---|---|---|---|
| house_id | Integer | No | — |
| bedrooms | Integer | No | — |
| bathrooms | Decimal | No | — |
| sqft_living | Integer | No | — |
| sqft_lot | Integer | No | — |
| floors | Decimal | No | — |
| waterfront | Integer | No | — |
| age | Integer | No | — |
| price | Decimal | No | — |
houses_1
House partition 1 with its source-visible column spellings.
| Column | Type | Nullable | Description |
|---|---|---|---|
| houseid | Integer | No | — |
| bed_rooms | Integer | No | — |
| bath_rooms | Decimal | No | — |
| sqftliving | Integer | No | — |
| sqft_lot | Integer | No | — |
| floors | Decimal | No | — |
| water_front | Integer | No | — |
| age | Integer | No | — |
| price | Decimal | No | — |
houses_2
House partition 2 with its source-visible column spellings.
| Column | Type | Nullable | Description |
|---|---|---|---|
| house_id | Integer | No | — |
| bedrooms | Integer | No | — |
| bathrooms | Decimal | No | — |
| sqft_living | Integer | No | — |
| sqftlot | Integer | No | — |
| floor | Decimal | No | — |
| waterfront | Integer | No | — |
| house_age | Integer | No | — |
| price | Decimal | No | — |
houses_3
House partition 3 with its source-visible column spellings.
| Column | Type | Nullable | Description |
|---|---|---|---|
| house_id | Integer | No | — |
| bedrooms | Integer | No | — |
| bathrooms | Decimal | No | — |
| living_sqft | Integer | No | — |
| lot_sqft | Integer | No | — |
| floors | Decimal | No | — |
| waterfront | Integer | No | — |
| age | Integer | No | — |
| house_price | Decimal | No | — |
house_views
House-view events; one viewer may view the same house more than once.
| Column | Type | Nullable | Description |
|---|---|---|---|
| viewer_id | Integer | No | — |
| house_id | Integer | No | — |
| view_date | Date | No | — |
Expected result
Your query or function must return these columns.
| Column | Type | Nullable | Description |
|---|---|---|---|
| house_id | Integer | No | — |
| bedrooms | Integer | No | — |
| bathrooms | Decimal | No | — |
| sqft_living | Integer | No | — |
| sqft_lot | Integer | No | — |
| floors | Decimal | No | — |
| waterfront | Integer | No | — |
| age | Integer | No | — |
| price | Decimal | No | — |
| num_views | Integer | No | — |
Row order: must match exactly. Numeric tolerance: 0.
Constraints
house_idvalues are unique across all four house partitions.- Every
house_views.house_ididentifies a supplied house. - Every
view_dateis a valid ISO calendar date. - The listed source-column variants are the only spellings that require normalization.
- Result row order is exact.
Source note: Original Capital One data-science assessment task (carousel slide 2).