HTQL Query Language Reference

HTQL is a query language that combines the data retrieval of SQL with the computational familiarity of Excel and the financial intelligence of a CFO’s toolkit.

If you have used Excel functions like NPV, IRR, or PMT, you already understand the mental model. HTQL takes that same approach and applies it to live business data — not static spreadsheets.

A single HTQL expression can retrieve orders, compute margins, evaluate risk, and return a validated result. There is no need for middleware, transformation layers, or external computation engines. The query is the computation.

The LOOKUP function retrieves data from any table. It works like a database query but reads like a business question.

Basic query:

LET orders = LOOKUP(orders, SHOW(order_id, total, margin), SORT(created_at), TAKE(50))

Filtered query:

LET vip_orders = LOOKUP(orders, WHERE(customer_segment = 'VIP'), SHOW(order_id, total, discount_applied), SORT(total), TAKE(100))

Joined query:

LET enriched = LOOKUP(orders, JOIN([customers], ON([orders.customer_id, customers.id])), WHERE(orders.total > 100), SHOW(orders.order_id, customers.name, orders.margin))

LOOKUP supports WHERE for filtering, SORT for ordering, TAKE for pagination, SHOW for column selection, and JOIN for combining tables. The syntax is intentionally readable — a finance team can understand what the query does without engineering support.

HTQL uses LET to assign variables and compose multi-step queries. This makes complex business logic readable and maintainable.

Computing margin on filtered orders:

LET recent = LOOKUP(orders, WHERE(created_at > '2026-01-01'), SHOW(order_id, revenue, cogs))
LET margin = GROSS_MARGIN(SUM(recent.revenue), SUM(recent.cogs))
RETURN {"total_orders": COUNT(recent), "gross_margin": margin}

Customer lifetime value from live data:

LET customers = LOOKUP(customers, WHERE(segment = 'VIP'), SHOW(customer_id, avg_order_value, purchase_frequency, retention_rate))
LET ltv = CLV(AVERAGE(customers.avg_order_value), 0.35, AVERAGE(customers.retention_rate), 0.10)
RETURN {"vip_ltv": ltv, "vip_count": COUNT(customers)}

Break-even analysis:

LET fixed = 45000
LET price = 89.99
LET variable = 38.50
LET units = BREAK_EVEN_QTY(fixed, price, variable)
RETURN {"break_even_units": units, "contribution_margin": CONTRIBUTION_MARGIN(price, variable)}

HTQL supports full CRUD operations with familiar syntax.

Insert:

INSERT {"product_id": "SKU-001", "price": 89.99, "cost": 38.50} INTO products

Update with filter:

UPDATE {"status": "archived"} IN promotions WHERE(end_date < '2026-03-01')

Upsert (insert or update):

UPSERT {"customer_id": "C-1234"} INSERT {"name": "Jane", "segment": "VIP"} UPDATE {"segment": "VIP"} IN customers

Bulk operations with FOR:

FOR order IN orders WHERE(status = 'pending') BEGIN
  UPDATE {"status": "reviewed"} IN orders WHERE(order_id = order.order_id)
END

All write operations execute within a single transaction. If any step fails, everything rolls back. No partial state.

HTQL includes a full library of statistical functions that work exactly like their Excel equivalents.

Descriptive statistics: AVERAGE, MEDIAN, STDEV, VAR, MIN, MAX, COUNT, COUNTA, COUNTBLANK, COUNTIF, COUNTIFS, SUM, SUMIF

Distribution functions: NORMDIST, NORMINV, TDIST, TINV, FDIST, FINV, BETADIST, BETAINV, BINOMDIST, POISSON, EXPONDIST

Regression and correlation: CORREL, FORECAST, SLOPE, INTERCEPT, RSQ, LINEST

Ranking and percentiles: RANK, PERCENTILE, PERCENTRANK, QUARTILE, LARGE, SMALL

Example — statistical analysis on live data:

LET sales = LOOKUP(orders, WHERE(created_at > '2026-01-01'), SHOW(total))
RETURN {
  "mean": AVERAGE(sales.total),
  "std_dev": STDEV(sales.total),
  "median": MEDIAN(sales.total),
  "p95": PERCENTILE(sales.total, 0.95)
}

HTQL is designed so that every business question — from a simple order lookup to a complex break-even analysis — can be answered in a single expression.

No transformation layers. No external computation engines. No spreadsheet workarounds.

The syntax is intentionally readable. A finance team can write queries. An engineering team can trust the results. And the system guarantees that every computation is deterministic, repeatable, and auditable.

If you can write an Excel formula, you can write HTQL.