Docs

rooms

Last updated: 27 Oct 2026

The rooms schema has one row per room type and night: the room nights sold, available and blocked, and each night's share of its stay's room revenue. Use it for occupancy, ADR, RevPAR and what is on the books for the nights ahead.

Use cases

  • Room KPIs. Show occupancy, adr and revpar by room_type, against the same weekdays last year with COMPARE TO stly.
  • Channel mix. Split room_nights_sold, room_revenue and adr by channel or sales_channel. Occupancy has no channel, because empty rooms weren't sold through one.
  • On the books. Query a future range, such as next_90_days, for the nights already sold. Add AS OF to see how it stood on an earlier day.
  • Blocked rooms. Count room_nights_blocked by room_block_type.
  • Weekday patterns. Group occupancy and adr by day_of_week.

Examples

Weekly management report

FROM rooms
  SHOW occupancy, adr, revpar, room_revenue
  DURING last_week
  COMPARE TO stly
  WITH PERCENT_CHANGE

Room types against last year

FROM rooms
  SHOW occupancy, adr, revpar
  DURING last_month
  GROUP BY room_type
  COMPARE TO stly
  WITH TOTALS, PERCENT_CHANGE

Channel mix

FROM rooms
  SHOW room_nights_sold, room_revenue, adr
  DURING last_month
  GROUP BY channel
  WITH TOTALS
  ORDER BY room_revenue DESC

The next three months on the books

FROM rooms
  SHOW occupancy, room_nights_sold, room_revenue
  DURING next_90_days
  GROUP BY month

Why rooms were blocked this year

room_block_type only applies to blocked nights. HAVING hides the row of sold and available nights, which have no block type.

FROM rooms
  SHOW room_nights_blocked
  DURING this_year
  GROUP BY room_block_type
  HAVING room_nights_blocked > 0

What it counts

Room nights sold, available and blocked per room type and stay night, with each night's share of the stay's room revenue.

Dates

Rows are dated by stay_night (default) or booking_date. ON picks one. Without DURING or SINCE, a query covers this_month. AS OF reads what was on the books at the end of an earlier day, and covers the 12 months from that day when the query has no range.

Metrics

Metrics you can use in FROM rooms.

  • room_nights_sold · Number

    Nights of stays that are neither cancelled nor discarded, including unassigned stays and no-shows.

    Groups and filters only by room_type, channel, sales_channel, package and time dimensions.

  • room_nights_available · Number

    Active rooms on each night less rooms a block covers, as rooms and blocks stand today.

    Dated by stay_night only. Groups and filters only by room_type and time dimensions.

  • room_nights_blocked · Number

    Rooms covered by a room block (maintenance, owner stay, group hold, staff or other) on each night, as blocks stand today. Group by room_block_type for why.

    Dated by stay_night only. Groups and filters only by room_type, room_block_type and time dimensions.

  • occupancy · Percent

    Room nights sold divided by room nights available. Can exceed 100% when stays sit in blocked or inactive rooms.

    Dated by stay_night only. Groups and filters only by room_type and time dimensions. Doesn't add up over time, so WITH CUMULATIVE can't take it.

  • room_revenue · Money

    Net room revenue (excluding VAT) after package allocation, spread over the stay's nights. Add-ons such as breakfast are not included.

    Groups and filters only by room_type, channel, sales_channel, package and time dimensions.

  • room_revenue_gross · Money

    Room revenue including VAT, spread over the stay's nights.

    Groups and filters only by room_type, channel, sales_channel, package and time dimensions.

  • adr · Money

    Average daily rate. Room revenue divided by room nights sold.

    Groups and filters only by room_type, channel, sales_channel, package and time dimensions. Doesn't add up over time, so WITH CUMULATIVE can't take it.

  • revpar · Money

    Revenue per available room. Room revenue divided by room nights available.

    Dated by stay_night only. Groups and filters only by room_type and time dimensions. Doesn't add up over time, so WITH CUMULATIVE can't take it.

  • guest_nights · Number

    Adults and children per sold room night. Infants are not counted.

    Groups and filters only by room_type, channel, sales_channel, package and time dimensions.

  • arrivals · Number

    Stays arriving, counted on their first night.

    Groups and filters only by room_type, channel, sales_channel, package and time dimensions.

Dimensions

Dimensions you can group and filter by in FROM rooms.

  • room_type · Text

    The accommodation (room type) a stay or capacity belongs to.

  • channel · Text

    Where the booking came from: an OTA such as Booking.com, POS, B2B, or Direct.

  • sales_channel · Text

    The kind of channel a booking was sold on: direct, ota, pos or b2b.

    Values: direct, ota, pos, b2b.

  • package · Text

    The package a line was sold in, if any.

  • room_block_type · Text

    Why rooms were blocked: maintenance, owner_stay, group_hold, staff or other, as blocks stand today. A room in two blocks on one night counts under the block made first. Sold and available room nights have none, so grouping room_nights_blocked by it adds a row with no block type and 0; HAVING room_nights_blocked > 0 hides it.

    Values: maintenance, owner_stay, group_hold, staff, other.