Dynamic Expressions

Beyond static values

The WHERE inside a WITH block is not limited to comparisons with fixed values. RevOpsQL supports dynamic expressions that compare properties against each other, or even against properties of the primary object.

L1 — Intra-object expressions

Comparing two properties of the same associated object:

-- Deals where the amount exceeds 10% of the allocated budget
SELECT name, domain
FROM company
WITH ASSOCIATED deal AS deals
  SELECT dealname, amount, budget
  WHERE amount > 0.1 * budget

Here, amount and budget are two properties of the deal. The comparison is performed for each deal individually.

L2 — Cross-object expressions

Comparing a property of the associated object with a property of the primary object, via the parent keyword:

-- Deals significant relative to the company's annual revenue
SELECT name, domain
FROM company
WITH ASSOCIATED deal AS significant_deals
  SELECT dealname, amount
  WHERE amount > 0.1 * parent.annual_revenue

parent refers to the company (the FROM object). parent.annual_revenue is therefore the revenue of the parent company of the deal.

Automatic: RevOpsQL automatically adds parent.xxx properties to the main query. You don't need to declare them in the main SELECT.

Available arithmetic operations

Operator	Meaning	Example
`*`	Multiplication	`0.1 * budget`
`/`	Decimal division	`amount / 12`
`//`	Integer division	`total // nb_deals`
`%`	Modulo	`id % 2`
`+`	Addition	`amount + bonus`
`-`	Subtraction	`budget - amount`
`-x`	Unary minus	`-discount`

Parentheses allow forcing a calculation order:

WHERE amount > (budget - costs) * 0.15

Mathematical functions

Function	Usage
`ABS(x)`	Absolute value
`FLOOR(x)`	Round down to nearest integer
`CEIL(x)`	Round up to nearest integer
`ROUND(x, n)`	Round to n decimal places
`COALESCE(x, default)`	Returns `default` if `x` is absent/null

-- Margin as a percentage, rounded to 2 decimal places
WHERE ROUND(margin / amount * 100, 2) > 15

-- Handle a potentially empty property
WHERE amount > COALESCE(min_threshold, 1000)

Behavior with missing values

If a property is absent in HubSpot, any expression involving it returns null, and the filter excludes the object. This is the secure default behavior.

Exception: COALESCE(prop, default) allows defining a fallback value:

-- Include deals without a budget by treating them as having a budget of 0
WHERE amount > COALESCE(budget, 0)