WHERE

WHERE keeps only some rows, before anything is added up. It filters on dimensions, the time axis and the weekday, week and month numbers. To filter on a metric, such as ADR above 1 500, use HAVING. To set the date range, use DURING or SINCE.

WHERE <condition> [AND | OR <condition>] …

condition:
  <dimension> = | != | < | <= | > | >= <value>
  <dimension> [NOT] IN (<value>, …)
  <dimension> [NOT] BETWEEN <low> AND <high>
  <dimension> [NOT] CONTAINS | STARTS WITH | ENDS WITH '<text>'
  <dimension> IS [NOT] NULL
  NOT (<condition>)

Compare with a value

Text values go in single quotes. Values of dimensions such as room_type, channel or product come from your own SERVOS data, and the editor checks them. A value you don't have gets an error that lists the closest ones you do. Dimensions with a fixed list, such as sales_channel (direct, ota, pos, b2b), are checked against that list.

Direct bookings only

FROM rooms
  SHOW room_nights_sold, room_revenue, adr
  WHERE sales_channel = 'direct'
  DURING last_month
  GROUP BY room_type

Summers, year by year

The time axis and its numbers work in WHERE too. month_of_year runs from 1 (January) to 12, week_of_year from 1 to 53, and day_of_week from 1 (Monday) to 7 (Sunday).

FROM rooms
  SHOW occupancy, adr
  WHERE month_of_year >= 6 AND month_of_year <= 8
  SINCE 2024-01-01
  GROUP BY year

Lists and ranges

Friday and Saturday nights

FROM rooms
  SHOW occupancy, adr
  WHERE day_of_week IN (5, 6)
  DURING last_month
  GROUP BY room_type

Last-minute bookings

lead_time_days is the number of days from booking to arrival.

FROM bookings
  SHOW bookings, avg_length_of_stay
  WHERE lead_time_days BETWEEN 0 AND 7
  DURING last_quarter
  GROUP BY channel

Everything but two OTAs

FROM bookings
  SHOW bookings, cancellation_rate
  WHERE channel NOT IN ('Booking.com', 'Expedia')
  DURING last_quarter
  GROUP BY month

Match text

CONTAINS, STARTS WITH and ENDS WITH match part of a text dimension, ignoring case. NOT in front of them turns the match around.

Every tour

FROM revenue
  SHOW net_revenue, units_sold
  WHERE product CONTAINS 'tour'
  DURING last_month
  GROUP BY product

Tours that aren't kayaking

FROM revenue
  SHOW net_revenue
  WHERE product ENDS WITH 'tour' AND product NOT CONTAINS 'kayak'
  DURING last_month
  GROUP BY product

Rows with no value

Some rows have no value for a dimension. A stay sold outside a package has no package, and a payment by card has no external_payment_method. IS NULL keeps those rows and IS NOT NULL leaves them out.

FROM revenue
  SHOW net_revenue
  WHERE package IS NOT NULL
  DURING last_month
  GROUP BY package

!=, NOT IN, NOT BETWEEN and the NOT text matches keep rows with no value. This query counts everything outside the autumn package, including lines sold outside any package:

FROM revenue
  SHOW net_revenue
  WHERE NOT (package = 'Autumn package')
  DURING last_month
  GROUP BY product

To leave out the rows with no value as well, add AND package IS NOT NULL.

Combine conditions

AND keeps rows that match both conditions, OR rows that match either, and NOT turns a condition around. AND binds tighter than OR, so use parentheses when you mix them.

FROM rooms
  SHOW room_nights_sold, adr
  WHERE room_type = 'Suite' AND (sales_channel = 'direct' OR channel = 'Expedia')
  DURING last_month
  GROUP BY channel

Metrics don't go in WHERE

WHERE runs before rows are added up, when there is no ADR or occupancy yet. Filter on metrics with HAVING.

FROM rooms
  SHOW adr
  WHERE adr > 1500
  GROUP BY room_type

Metrics that can't filter every way

A metric filters by the same dimensions it can split by. Occupancy has no channel, because an empty room wasn't sold through one, so it can't be filtered by channel either. Each schema page says which dimensions such metrics take.

FROM rooms
  SHOW occupancy
  WHERE channel = 'Booking.com'
  DURING last_month