Analyze House and Viewing Metrics
Capital One● EasyFULLTIMEOAProblem statement
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 ofpriceacross every house.percentage_waterfront_houses: the percentage of houses whosewaterfrontvalue is1, on the0-to-100scale.average_views_per_viewed_house_last_30_days: use view dates from2023-03-16through2023-04-15, inclusive; count views per house, then average those counts across houses with at least one qualifying view. Return0when no house has a qualifying view.
Table schema
Pandas
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
One partition of the house catalog.
| Column | Type | Nullable | Description |
|---|---|---|---|
| house_idPK | 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
One partition of the house catalog.
| Column | Type | Nullable | Description |
|---|---|---|---|
| house_idPK | 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_2
One partition of the house catalog.
| Column | Type | Nullable | Description |
|---|---|---|---|
| house_idPK | 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_3
One partition of the house catalog.
| Column | Type | Nullable | Description |
|---|---|---|---|
| house_idPK | 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 | — |
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 |
|---|---|---|---|
| insight_type | Text | No | — |
| value | Decimal | No | — |
Row order: must match exactly. Numeric tolerance: 1e-9.
Constraints
- At least one house exists across the four partitions.
house_idvalues are unique across all four house partitions.waterfrontis0or1.- Every
priceis finite and nonnegative. - Every
view_dateis a valid ISO calendar date. - Result row order is exact.
Source note: Original Capital One data-science assessment task (carousel slide 1).