Quick Reference

Complete query structure

SELECT prop1, prop2, ...
FROM object_type
[ WHERE conditions ]
[ LIMIT n ORDER BY { RECENTLY_MODIFIED | OLDEST_MODIFIED | RECENTLY_CREATED | OLDEST_CREATED } ]
[ WITH [INNER] ASSOCIATED object_type
    [ON LABELS 'label1', 'label2']
    AS alias
    [SELECT prop1, prop2]
    [WHERE conditions]
    [REQUIRE COUNT op n]
    [REQUIRE COUNT BETWEEN n, m]
    [REQUIRE {ANY|ALL|NONE} prop op expression]
    [TAKE n ORDER BY sort_criteria]
  ...
]

Comparison operators

Symbolic	Keyword
`=`	`EQUAL`
`!=`	`NOT_EQUAL`
`>`	`GREATER THAN`
`>=`	`AT LEAST`
`<`	`LESS THAN`
`<=`	`AT MOST`
`BETWEEN x AND y`	—
`IN ('a','b')`	—
`NOT_IN ('a','b')`	—
`HAS_PROPERTY`	—
`NOT_HAS_PROPERTY`	—
`CONTAINS_TOKEN`	—
`NOT_CONTAINS_TOKEN`	—
`IN <date_period>`	—

Date periods

THIS-WEEK           THIS-WEEK-SO-FAR
THIS-MONTH          THIS-MONTH-SO-FAR
THIS-QUARTER        THIS-QUARTER-SO-FAR
THIS-YEAR           THIS-YEAR-SO-FAR
LAST-WEEK           LAST-WEEK-SO-FAR
LAST-MONTH          LAST-MONTH-SO-FAR
LAST-QUARTER        LAST-QUARTER-SO-FAR
LAST-YEAR           LAST-YEAR-SO-FAR
LAST-N-DAYS         (e.g.: LAST-30-DAYS)

Available functions in expressions

ABS(x)              FLOOR(x)            CEIL(x)
ROUND(x, n)         COALESCE(x, default)

Expression levels

Level	Form	Available in
L0 — static	`'value'`, `5000`	Main WHERE + WITH WHERE
L1 — intra-object	`amount > 0.1 * budget`	WITH WHERE only
L2 — cross-object	`amount > 0.1 * parent.annual_revenue`	WITH WHERE only

Evaluation rules

Element	Evaluated	Implication
Main `WHERE`	HubSpot side	Static only, most efficient
`WITH WHERE`	RevOpsQL side	Supports L1 and L2
`LIMIT`	HubSpot side	Reduces API calls
`TAKE`	RevOpsQL side	Does not reduce API calls
`REQUIRE`	RevOpsQL side	Filters primary object, not associated list

Complete example combining all features

SELECT name, domain
FROM company
WHERE annualrevenue > 1000000
  AND createdate IN THIS-YEAR-SO-FAR
WITH INNER ASSOCIATED deal ON LABELS 'primary deal' AS top_deals
  SELECT dealname, amount, dealstage
  WHERE dealstage = 'closedwon'
    AND amount > 5000
  REQUIRE ANY amount GREATER THAN 10000
  TAKE 3 ORDER BY amount DESC
WITH ASSOCIATED contact ON LABELS 'decision maker' AS decision_makers
  SELECT firstname, lastname, email
  WHERE email HAS_PROPERTY
  REQUIRE COUNT AT LEAST 1
  TAKE 5 ORDER BY OLDEST_CREATED

"Companies with more than €1M in revenue created this year, that have at least one primary closed-won deal above €10,000 — returning their top 3 deals and their 5 oldest decision makers (with email filled in)."