COMPARE TO - Syntax

COMPARE TO adds each metric for another period, next to the current one. List several comparisons to get several columns. Add WITH PERCENT_CHANGE for the change in percent.

COMPARE TO <comparison or period> [UNTIL <day>], …

Relative comparisons

Comparison	Compares with
`previous_period`	as many days, just before the range
`previous_day`	the same dates a day earlier
`previous_week`	the same dates a week earlier
`previous_month`	the same dates a month earlier
`previous_quarter`	the same dates three months earlier
`previous_year`	the same dates a year earlier
`stly`	same time last year: 364 days earlier, so Mondays line up with Mondays

previous_year_match_day_of_week is another name for stly. A month-based comparison of a range that ends on a month's last day ends on the last day of the earlier month, so previous_month of 1–31 October is 1–30 September.

Use stly when weekdays matter, as they do for hotels: a Saturday is compared with a Saturday. Use previous_year for calendar dates, such as Christmas Eve against Christmas Eve.

The last 30 days against the 30 before

FROM revenue
  SHOW net_revenue
  DURING last_30_days
  COMPARE TO previous_period
  WITH PERCENT_CHANGE

Room types against the same time last year

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

Fixed periods

COMPARE TO also takes a fixed period, for comparisons the relative ones can't make, such as Easter against last Easter.

Written	Compares with
a named range or a period, such as `last_year` or `2025-09`	that whole period
a day, such as `2025-09-01`	a period that starts on that day, as long as the range
a day `UNTIL` a day	the days from one to the other

Dates, offsets and date functions all work. A named range is compared whole, so COMPARE TO last_year compares last month with all of last year. For the same dates a year earlier, use previous_year.

Easter week against last Easter week

FROM rooms
  SHOW room_revenue, occupancy
  SINCE 2026-04-02 UNTIL 2026-04-09
  COMPARE TO 2025-04-17 UNTIL 2025-04-24
  WITH PERCENT_CHANGE

A month against two other months

FROM revenue
  SHOW net_revenue
  DURING 2026-09
  GROUP BY revenue_category
  COMPARE TO 2025-09, 2026-08

UNTIL right after a fixed period ends that period. After a relative comparison such as stly, it ends the query's own range, as it would anywhere else.

A fixed period that is much longer or shorter than the range gets a warning: more than 3 days, or more than a ninth of the range when that is less. The query still runs.

Comparison columns

Each comparison adds a column per metric, named after the metric and the comparison. A fixed period is named after how it starts.

Comparison	Column
`COMPARE TO stly`	`room_revenue__stly`
`COMPARE TO previous_year_match_day_of_week`	`room_revenue__stly`
`COMPARE TO last_month`	`room_revenue__last_month`
`COMPARE TO 2025-09`	`room_revenue__period_2025_09`
`COMPARE TO 2025-04-17 UNTIL 2025-04-24`	`room_revenue__period_2025_04_17`
`COMPARE TO startOfMonth(-13m)`	`room_revenue__start_of_month_sub_13m`
`WITH PERCENT_CHANGE`	`room_revenue__stly_pct_change`

ORDER BY can sort by these columns.

Over time

With TIMESERIES or a time GROUP BY, each period is paired with the same period of the comparison: day 1 of this month next to day 1 of the month it is compared with.

By day, previous_month, previous_quarter and fixed periods line up whole days, so each day is compared with one day. A day the comparison has more of than the range, such as 31 August against a 30-day September, has no row of its own, but still counts in the totals row.

This month's room revenue, day by day, against last year

FROM rooms
  SHOW room_revenue
  DURING this_month
  TIMESERIES day
  COMPARE TO stly
  WITH CUMULATIVE

Comparing what was on the books

With AS OF, each comparison is read as far before its own range as AS OF is before the current one. COMPARE TO stly then sets this year's bookings against last year's at the same lead time. That is pace.