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