data/extra_instructions.md

# Extra Instructions

## Room type filter (exact)
- Use `room_type = 'Entire home/apt'`
- Use `room_type = 'Private room'`
- Use `room_type = 'Shared room'`
- Use `room_type = 'Hotel room'`
- Text matches are **case- and punctuation-sensitive**.
- Always use the exact string as shown in the dataset.

## Neighbourhood filter (exact)
- Use `neighbourhood = 'Short North'` (example).
- Neighbourhood names are **case-sensitive**.
- Always match the spelling exactly as stored in the dataset.
- Do not assume automatic capitalization or fuzzy matching.

## Price queries (nightly rate)
- `price` represents the nightly rental rate in USD.
- Price is stored as a whole-dollar integer.
- If asking for:
  - “Under $X” → use `price < X`
  - “At most $X” → use `price <= X`
- Weekly or monthly calculations must be explicitly requested.
  - Weekly = `price * 7`
  - Monthly (approximate) = `price * 30`

## Handling listings with zero reviews
- If `number_of_reviews = 0`:
  - `last_review` is blank.
  - `reviews_per_month` is NULL.
- Do not assume NULL `reviews_per_month` equals 0 unless explicitly stated.
- When calculating averages of `reviews_per_month`, exclude NULL values unless instructed otherwise.

## Availability interpretation
- `availability_365` represents the number of available days in the next 365 days.
- `availability_365 = 0` may indicate:
  - Fully booked
  - Host-blocked calendar
  - Inactive listing
- Do not automatically interpret 0 as permanently delisted unless specified.

## Host analysis (multi-listing hosts)
- Use `calculated_host_listings_count` to identify hosts with multiple listings.
- Multi-listing host condition:
  - `calculated_host_listings_count > 1`
- When ranking hosts by inventory, sort by `calculated_host_listings_count` (descending).

## Aggregation intent (very important)
State whether you want raw rows or an aggregate.

You can copy/paste:

- **Calculate total listings:** `COUNT(id)`
- **Calculate average price:** `AVG(price)`
- **Show rows:** return matching listings without aggregation

## Rounding and numeric handling
- `price` is already stored as whole dollars.
- When computing averages, results may return decimals.
- Do not round unless explicitly requested.

## Verification step (recommended)
If a filter may match multiple neighbourhood spellings or room types:
- First show matching rows to confirm exact labels before aggregating.

Example:
- “Show listings where neighbourhood = 'Short North' so I can confirm label before calculating.”

## Action wording (so I take the right next step)
Start your request with:
- **Calculate** / **Compute** → run aggregation and return computed results
- **Show** / **Filter to** → return matching listing rows