HORIZON JOIN keyword

HORIZON JOIN is a specialized time-series join designed for markout analysis — a common financial analytics pattern where you need to analyze how prices or metrics evolve at specific time offsets relative to events (e.g., trades, orders).

It is a variant of the JOIN keyword that runs an ASOF JOIN match at each of a set of forward (or backward) time offsets from every left-hand row, in a single pass. Adding aggregate functions collapses those matches into one row per offset, or per offset and key, which is how markout curves are built.

HORIZON JOIN supports joining against multiple right-hand-side tables in a single query, matching columns from several time-series sources against a common left-hand table and offset grid.

To see how it compares with WINDOW JOIN and the other time-series joins, read Which time-series join?

Syntax

RANGE form

Generate offsets at regular intervals from FROM to TO (inclusive) with the given STEP:

HORIZON JOIN with RANGE

SELECT [<keys>,] [<aggregations>]
FROM <left_table> AS <left_alias>
HORIZON JOIN <right_table_1> AS <alias_1> [ON (<join_keys_1>)]
[HORIZON JOIN <right_table_2> AS <alias_2> [ON (<join_keys_2>)]]
...
HORIZON JOIN <right_table_N> AS <alias_N> [ON (<join_keys_N>)]
RANGE FROM <from_expr> TO <to_expr> STEP <step_expr> AS <horizon_alias>
[WHERE <left_table_filter>]
[GROUP BY <keys>]
[ORDER BY ...]

For example, RANGE FROM -3m TO 3m STEP 1m generates offsets at -3m, -2m, -1m, 0m, 1m, 2m, 3m.

LIST form

Specify explicit offsets as interval literals:

HORIZON JOIN with LIST

SELECT [<keys>,] [<aggregations>]
FROM <left_table> AS <left_alias>
HORIZON JOIN <right_table_1> AS <alias_1> [ON (<join_keys_1>)]
[HORIZON JOIN <right_table_2> AS <alias_2> [ON (<join_keys_2>)]]
...
HORIZON JOIN <right_table_N> AS <alias_N> [ON (<join_keys_N>)]
LIST (<offset_expr>, ...) AS <horizon_alias>
[WHERE <left_table_filter>]
[GROUP BY <keys>]
[ORDER BY ...]

For example, LIST (-1m, -5s, -1s, 0, 1s, 5s, 1m) generates offsets at those specific points. Offsets must be monotonically increasing. Unitless 0 is allowed as shorthand for zero offset.

When using multiple HORIZON JOINs, only the last HORIZON JOIN in the chain carries the RANGE/LIST and AS clauses. Preceding HORIZON JOINs omit them. Each right-hand table can independently use or omit the ON clause — you can freely mix keyed and non-keyed (timestamp-only ASOF) joins within the same query.

How it works

For each row in the left-hand table and each offset in the horizon:

1. Compute left_timestamp + offset
2. Perform an ASOF match against each right-hand table at that computed timestamp
3. When join keys are provided (via ON), consider only the right-hand rows matching the keys

With multiple right-hand tables, QuestDB matches each table, at each offset, independently. If a right-hand table has no match for a given row/offset combination, its columns resolve to NULL.

QuestDB implicitly groups the results by the non-aggregate SELECT columns (horizon offset, left-hand table keys, etc.), and applies aggregate functions across all matched rows. Aggregate expressions can reference columns from different right-hand tables (e.g., avg(b.bid + a.ask)).

Aggregate functions are optional. Without them, the implicit grouping still applies, so the result has one row per distinct combination of the selected columns, as with SELECT DISTINCT. To get one row per left-hand row and offset, select the left table's timestamp along with the columns you need.

The horizon pseudo-table

The RANGE or LIST clause defines a virtual table of time offsets, aliased by the AS clause. This pseudo-table exposes two columns:

Column	Type	Description
<alias>.offset	LONG	The offset value in the left-hand table's designated timestamp resolution. For example, with microsecond timestamps, h.offset / 1_000_000 converts to seconds; with nanosecond timestamps, h.offset / 1_000_000_000 converts to seconds.
<alias>.timestamp	TIMESTAMP	The computed horizon timestamp (left_timestamp + offset). Available for grouping or expressions.

Interval units

All offset values in RANGE (FROM, TO, STEP) and LIST must include a unit suffix. Bare numbers are not valid — write 5s, not 5 or 5_000_000_000. The only exception is 0, which is allowed without a unit as shorthand for zero offset.

Both RANGE and LIST use the same interval expression syntax as SAMPLE BY:

Unit	Meaning
n	Nanoseconds
U	Microseconds
T	Milliseconds
s	Seconds
m	Minutes
h	Hours
d	Days

Note that h.offset is always returned as a LONG in the left-hand table's timestamp resolution (e.g., nanoseconds for TIMESTAMP_NS tables), regardless of the unit used in the RANGE or LIST definition. When matching offset values in a PIVOT ... FOR offset IN (...) clause, use the raw numeric value (e.g., 1_800_000_000_000 for 30 minutes in nanoseconds), not the interval literal.

GROUP BY rules

The GROUP BY clause is optional. When omitted, results are implicitly grouped by all non-aggregate SELECT columns. When GROUP BY is present, it follows stricter rules than regular GROUP BY:

• Each GROUP BY expression must exactly match a non-aggregate SELECT expression (with table prefix tolerance, e.g., t.symbol matches symbol) or a SELECT column alias.
• Every non-aggregate SELECT column must appear in GROUP BY.
• Column index references are supported (e.g., GROUP BY 1, 2).

For example, if the SELECT list contains h.offset / 1_000_000_000 AS horizon_sec, the GROUP BY must use either the alias horizon_sec or the full expression h.offset / 1_000_000_000 — using just h.offset is not valid because it does not exactly match any non-aggregate SELECT expression.

Examples

The examples below use the demo dataset tables fx_trades (trade executions) and market_data (order book snapshots with 2D arrays for bids/asks).

Post-trade markout at uniform horizons

Measure the average mid-price at 5-second intervals after each trade — a classic way to evaluate execution quality and price impact:

Post-trade markout curveDemo this query

SELECT
    h.offset / 1_000_000_000 AS horizon_sec,
    t.symbol,
    avg((m.best_bid + m.best_ask) / 2) AS avg_mid
FROM fx_trades AS t
HORIZON JOIN market_data AS m ON (symbol)
RANGE FROM 0s TO 1m STEP 5s AS h
WHERE t.timestamp IN '$now-1h..$now'
ORDER BY t.symbol, horizon_sec;

Since fx_trades uses nanosecond timestamps (TIMESTAMP_NS), h.offset is in nanoseconds. Dividing by 1,000,000,000 converts to seconds.

Markout P&L at non-uniform horizons

Compute the average post-trade markout value at specific horizons using LIST:

Markout at specific time pointsDemo this query

SELECT
    h.offset / 1_000_000_000 AS horizon_sec,
    t.symbol,
    avg((m.best_bid + m.best_ask) / 2 - t.price) AS avg_markout
FROM fx_trades AS t
HORIZON JOIN market_data AS m ON (symbol)
LIST (0, 5s, 30s, 1m) AS h
WHERE t.timestamp IN '$now-1h..$now'
ORDER BY t.symbol, horizon_sec;

Pre- and post-trade price movement

Use negative offsets to see price levels before and after trades — useful for detecting information leakage or adverse selection:

Price movement around trade eventsDemo this query

SELECT
    h.offset / 1_000_000_000 AS horizon_sec,
    t.symbol,
    avg((m.best_bid + m.best_ask) / 2) AS avg_mid,
    count() AS sample_size
FROM fx_trades AS t
HORIZON JOIN market_data AS m ON (symbol)
RANGE FROM -5s TO 5s STEP 1s AS h
WHERE t.timestamp IN '$now-1h..$now'
ORDER BY t.symbol, horizon_sec;

Volume-weighted markout

Compute an overall volume-weighted markout value without grouping by symbol:

Volume-weighted markout across all symbolsDemo this query

SELECT
    h.offset / 1_000_000_000 AS horizon_sec,
    sum(((m.best_bid + m.best_ask) / 2 - t.price) * t.quantity)
        / sum(t.quantity) AS vwap_markout
FROM fx_trades AS t
HORIZON JOIN market_data AS m ON (symbol)
RANGE FROM 0s TO 5m STEP 30s AS h
WHERE t.timestamp IN '$now-1h..$now'
ORDER BY horizon_sec;

Multi-table: consolidated and ECN-level quotes around trades

Join against two right-hand tables in a single query - market_data for consolidated book prices and core_price for ECN-level quotes - to compare how both track around each EURUSD trade:

Multi-table HORIZON JOIN with demo dataDemo this query

SELECT
    h.offset / 1000000 AS horizon_ms,
    t.symbol,
    avg(m.best_bid) AS consolidated_bid,
    avg(c.bid_price) AS ecn_bid
FROM fx_trades AS t
HORIZON JOIN market_data AS m
    ON (t.symbol = m.symbol)
HORIZON JOIN core_price AS c
    ON (t.symbol = c.symbol AND t.ecn = c.ecn)
    LIST (-1s, 0, 1s, 5s) AS h
WHERE t.symbol = 'EURUSD'
    AND t.timestamp IN '$now-1h..$now'
GROUP BY horizon_ms, t.symbol
ORDER BY t.symbol, horizon_ms;

Note that the LIST clause appears only on the last HORIZON JOIN. Each right-hand table can independently use or omit ON.

Multi-table: bid and ask spread (synthetic example)

Join against two separate tables (bids and asks) in a single HORIZON JOIN query to compute the average spread at each horizon:

Multi-table HORIZON JOIN with bid/ask spread

SELECT
    h.offset / 1_000_000_000 AS horizon_sec,
    t.symbol,
    avg(b.bid) AS avg_bid,
    avg(a.ask) AS avg_ask,
    avg(a.ask - b.bid) AS avg_spread
FROM trades AS t
HORIZON JOIN bids AS b ON (t.symbol = b.symbol)
HORIZON JOIN asks AS a ON (t.symbol = a.symbol)
    RANGE FROM -2s TO 2s STEP 2s AS h
GROUP BY horizon_sec, t.symbol
ORDER BY t.symbol, horizon_sec;

Multi-table: keyed and non-keyed mix

Combine a keyed join (matching by symbol) with a non-keyed join (timestamp-only ASOF) in the same query — useful when one source is symbol-specific and another is market-wide:

Mixed keyed and non-keyed HORIZON JOIN

SELECT
    avg(p.price) AS avg_price,
    avg(r.rate) AS avg_rate
FROM trades AS t
HORIZON JOIN prices AS p ON (t.symbol = p.symbol)
HORIZON JOIN rates AS r
    LIST (0, 1s, 5s) AS h;

Here prices is matched by symbol (keyed), while rates uses timestamp-only ASOF matching (non-keyed).

Multi-table: three right-hand tables

HORIZON JOIN supports more than two right-hand tables:

Three-table HORIZON JOIN

SELECT
    avg(b.bid) AS avg_bid,
    avg(a.ask) AS avg_ask,
    avg(m.mid) AS avg_mid
FROM trades AS t
HORIZON JOIN bids AS b ON (t.symbol = b.symbol)
HORIZON JOIN asks AS a ON (t.symbol = a.symbol)
HORIZON JOIN mids AS m ON (t.symbol = m.symbol)
    LIST (0) AS h;

Mixed-precision timestamps

The left-hand and right-hand tables can use different timestamp resolutions (e.g., TIMESTAMP with microseconds and TIMESTAMP_NS with nanoseconds). QuestDB aligns the timestamps internally — no explicit casting is needed.

When the tables differ in resolution, h.offset uses the resolution of the left-hand table (the event table).

Parallel execution

QuestDB can execute HORIZON JOIN queries in parallel across multiple worker threads. Use EXPLAIN to see the execution plan and verify parallelization:

Analyze HORIZON JOIN execution planDemo this query

EXPLAIN SELECT
    h.offset / 1_000_000_000 AS horizon_sec,
    t.symbol,
    avg((m.best_bid + m.best_ask) / 2) AS avg_mid
FROM fx_trades AS t
HORIZON JOIN market_data AS m ON (symbol)
RANGE FROM -1m TO 1m STEP 5s AS h
WHERE t.timestamp IN '$now-1h..$now'
ORDER BY t.symbol, horizon_sec;

Look for these indicators in the plan:

• Async Horizon Join: Parallel execution using Java-evaluated expressions
• Async JIT Horizon Join: Parallel execution using Just-In-Time-compiled expressions for better performance
• Async Multi Horizon Join: Parallel execution with multiple right-hand tables (shown with tables: N indicating the number of right-hand tables)

Current limitations

• No other join types: HORIZON JOIN cannot be combined with other join types (e.g., JOIN, ASOF JOIN) in the same level of the query. Multiple HORIZON JOINs are allowed, but mixing with non-HORIZON joins is not. Other joins can be done in an outer query.
• No window functions: Window functions cannot be used in HORIZON JOIN queries. Wrap the HORIZON JOIN in a subquery and apply window functions in the outer query.
• No SAMPLE BY: SAMPLE BY cannot be used with HORIZON JOIN. Use GROUP BY with a time-bucketing expression instead.
• WHERE filters left-hand table only: The WHERE clause can only reference columns from the left-hand table. References to any right-hand table columns or horizon pseudo-table columns (h.offset, h.timestamp) are not allowed.
• All tables must have a designated timestamp: The left-hand and all right-hand tables must each have a designated timestamp column.
• Right-hand side must be a table: Each right-hand side of HORIZON JOIN must be a table with an optional filter, more complex subqueries aren't supported.
• Left-hand side queries are restricted as well. On the left hand side, the query that works best is a table with an optional filter. Some other query types are also supported, but they degrade the query plan to single-threaded processing.
• RANGE constraints: STEP must be positive; FROM must be less than or equal to TO.
• LIST constraints: Offsets must be interval literals (e.g., 1s, -2m, 0) and monotonically increasing.

Hand-rolled markout curves

If you express a markout curve as a CROSS JOIN against a table of offsets rather than as a HORIZON JOIN, the markout_horizon hint avoids materializing and sorting the whole join output.

Related documentation

• ASOF JOIN
• JOIN
• PIVOT
• SAMPLE BY
• Markout analysis recipe
• markout_horizon optimizer hint