Syntax

A SERVOS QL query is a list of clauses. FROM picks a schema, SHOW picks the metrics, and every other clause filters, dates, groups, compares or charts the result.

FROM rooms
  SHOW room_nights_sold, room_revenue, adr
  WHERE sales_channel = 'ota'
  DURING last_month
  GROUP BY room_type
  ORDER BY room_revenue DESC
  LIMIT 10

Anatomy of a query

Every query needs FROM and a metric to show. SHOW lists the metrics. A query that charts its metrics with VISUALIZE can leave SHOW out.

Clauses can come in any order, and each clause can appear once. When you run a query, the editor writes its clauses back one per line, in the order on this page. Unlike SQL, you set the date range with DURING or SINCE, not with WHERE.

Clause	What it does
`FROM`	Picks the schema. Required.
`SHOW`	Lists the metrics and calculations to return.
`WHERE`	Keeps only some rows, before anything is added up.
`ON`	Picks which date the range applies to, such as stay night or booking date.
`DURING`, `SINCE`, `UNTIL`	Sets the date range.
`AS OF`	Reads what was on the books at the end of an earlier day.
`GROUP BY`	Splits the result by dimensions and time periods.
`TIMESERIES`	Returns every day, week or month in the range, empty ones as 0.
`COMPARE TO`	Adds each metric for another period.
`HAVING`	Keeps only some groups, after metrics are computed.
`ORDER BY`	Sorts the rows.
`LIMIT`	Caps the number of rows.
`WITH`	Adds a totals row, percent change or running totals.
`VISUALIZE`	Picks the chart.

Names and keywords

Keywords such as FROM and GROUP BY work in any case, and so do the language's own words, such as last_month or TOTALS. This query runs as written:

from rooms show occupancy during last_month group by room_type with totals

Names of schemas, metrics and dimensions are lowercase, with underscores between words: room_nights_sold, not Room Nights Sold or ROOM_NIGHTS_SOLD. SERVOS QL doesn't read OCCUPANCY as occupancy, so a typo never runs as something else.

Strings

Text values go in single quotes. Write a quote inside a string twice.

FROM revenue
  SHOW net_revenue
  WHERE product = 'Kayak tour'
  DURING last_month

'Fisher''s cabin' is the text Fisher's cabin. Double quotes don't make a string.

Numbers

A number next to a metric is in the unit you read it in. Money is in whole currency units and percentages are in percent, so HAVING adr > 1500 means an ADR above 1 500 in your currency, and HAVING occupancy > 80 means above 80%.

Comments

-- comments out the rest of a line, and /* … */ comments out everything between. When the editor formats a query, each comment stays with its clause. The controls drop comments when they rewrite the query.

-- Last month's channel mix
FROM rooms
  SHOW room_nights_sold /* sold, not booked */
  DURING last_month
  GROUP BY channel

When a query has a problem

The editor checks the query as you type and marks each problem in place. An error stops the query from running. A warning lets it run and says why the result may surprise you. Each message says what is wrong and what would work, and Learn more opens the page of these docs that explains it.

This query filters on a metric in WHERE. Metrics only exist once rows are added up, so the editor says to use HAVING:

FROM rooms
  SHOW adr
  WHERE adr > 1500
  GROUP BY room_type

FROM rooms
  SHOW adr
  GROUP BY room_type
  HAVING adr > 1500

The editor calls a schema a dataset. The two words mean the same thing.