Swaps by Pair

Question it answers

“Give me every DEX trade on pool/pair 0x…, newest first, each row is one trade with both token legs, the side, the protocol, and a USD notional.”

Same trade projection as Swaps by Token, but keyed by the pool/pair address with one row per trade (no per-token-side unpivot). pool_address is the pair address for direct pool fills and the aggregator address for aggregator-routed trades.

What you get

One row per trade, keyed by pool_address:

Column	Description
`pool_address`	Pair address (pool fills) or aggregator address (aggregator-routed trades)
`token0_address`, `token1_address`	The pair’s two tokens
`amount0`, `amount1`	Raw token-unit deltas for each leg (`uint256` as text)
`side`	`buy` · `sell`, derived from the base token’s pool-balance delta
`source_kind`	`pool_fill` (direct pool) · `aggregator` (aggregator-routed)
`price_usd`	Bought-leg (`token1`) USD price; `NULL` with no in-block price update
`notional_usd`	`token1Amount / 10^token1Decimals × price_usd`; `NULL` if unpriced
`protocol`	DEX/protocol identifier
`fee_amount`, `fee_token`, `fee_usd`	Trade fee, pool fills only (`NULL` on aggregator rows)
`trader_address`	The trading wallet
`block_number`, `event_ts`, `tx_hash`, `vendor_event_id`	On-chain ordering and identity

Source

The transform reads two per-block trade arrays and projects them into one feed: tokenSwaps (direct pool fills, source_kind='pool_fill', pool_address = pairAddress) · aggregateTokenSwaps (aggregator-routed trades, source_kind='aggregator', pool_address = aggregatorAddress) The two branches are disjoint by construction, together they give every fill exactly once. USD values are computed inline from the same block’s tokenPriceUpdates (the chronologically-last update wins on duplicate keys), no separate price join at read time.

Destination

Destination	Table	By-pair access
ClickHouse (first-class)	`fact_swaps_by_pair`	Prefix scan on `(chain_id, pool_address, event_ts, …)`; read with `FINAL` or `sum(sign)`
Postgres	`swaps`	Index lead `(pool_address, block_number DESC)`
MySQL	`swaps`	Index lead `(pool_address, block_number)`

ClickHouse uses the collapsing log-table pattern (see the recipes overview) so chain reorganizations self-correct, the +1/−1 reorg pair for a trade shares an identical key and collapses cleanly. The fact table’s sort key is pool-first, so a pair’s full trade feed is a contiguous range read.

Full schema

The complete read table this recipe produces, one row per trade. Keep the columns you need and drop the rest (see Schema & flexibility). Raw uint256 amounts are stored as text in ClickHouse (they exceed numeric precision); USD columns are nullable so an unpriced token leaves them empty.

ClickHouse, fact_swaps_by_pair

CREATE TABLE recipe_swaps_by_pair.fact_swaps_by_pair
(
    vendor_event_id     String,
    ingested_at         DateTime64(3),
    chain_id            UInt32,
    block_hash          String,
    block_number        UInt64,
    event_ts            DateTime64(3),
    pool_address        String,        -- pair (pool_fill) or aggregator
    token0_address      String,
    token1_address      String,
    amount0             String,        -- raw uint256
    amount1             String,        -- raw uint256
    trader_address      String,
    price_usd           Nullable(String),
    notional_usd        Nullable(String),
    side                LowCardinality(String),   -- buy | sell
    source_kind         LowCardinality(String),   -- aggregator | pool_fill
    protocol            LowCardinality(String),
    fee_amount          Nullable(String),
    fee_token           Nullable(String),
    fee_usd             Nullable(String),
    sign                Int8
)
ENGINE = ReplicatedCollapsingMergeTree(
    '/clickhouse/tables/{database}/fact_swaps_by_pair', '{replica}', sign)
PARTITION BY (chain_id, toYYYYMM(event_ts))
ORDER BY (chain_id, pool_address, event_ts, vendor_event_id);

The sign column drives reorg collapsing, read with FINAL or sum(sign), never a bare WHERE sign=1. A single-node setup can use CollapsingMergeTree(sign) without the replication path.

Postgres, swaps

CREATE TABLE public.swaps (
  position           BIGINT      NOT NULL,
  log_index          BIGINT,                 -- NULL for aggregator rows
  transaction_index  BIGINT,
  block_number       BIGINT      NOT NULL,
  block_timestamp    BIGINT      NOT NULL,    -- unix seconds
  tx_hash            TEXT        NOT NULL,
  vendor_event_id    TEXT        NOT NULL,
  trader_address     TEXT        NOT NULL,
  pool_address       TEXT        NOT NULL,    -- pair (pool_fill) or aggregator
  token0_address     TEXT        NOT NULL,
  token1_address     TEXT        NOT NULL,
  amount0            NUMERIC(76, 0)  NOT NULL,
  amount1            NUMERIC(76, 0)  NOT NULL,
  price_usd          NUMERIC(38, 18),
  notional_usd       NUMERIC(38, 18),
  side               TEXT        NOT NULL,
  source_kind        TEXT        NOT NULL,    -- aggregator | pool_fill
  protocol           TEXT        NOT NULL,
  fee_amount         NUMERIC(76, 0),
  fee_token          TEXT,
  fee_usd            NUMERIC(38, 18)
);

-- By-pair access (the recipe's primary purpose).
CREATE INDEX IF NOT EXISTS swaps_pool_block_idx
  ON public.swaps (pool_address, block_number DESC);
-- Token + trader + block-range helpers.
CREATE INDEX IF NOT EXISTS swaps_token0_block_idx
  ON public.swaps (token0_address, block_number DESC);
CREATE INDEX IF NOT EXISTS swaps_token1_block_idx
  ON public.swaps (token1_address, block_number DESC);
CREATE INDEX IF NOT EXISTS swaps_trader_block_idx
  ON public.swaps (trader_address, block_number DESC);
CREATE INDEX IF NOT EXISTS swaps_block_idx
  ON public.swaps (block_number);

MySQL is the same shape with DECIMAL(38,18) for the USD columns. Amounts use NUMERIC(76,0) so large raw uint256 balances never overflow. position is the block-level cursor used during backfill.

Example reads

All trades on a pool/pair, newest first (ClickHouse):

SELECT event_ts, side, source_kind, token0_address, token1_address,
       amount0, amount1, notional_usd, protocol
FROM recipe_swaps_by_pair.fact_swaps_by_pair FINAL
WHERE chain_id = 1
  AND pool_address = lower('0x88e6A0c2dDD26FEEb64F039a2c41296FcB3f5640')
ORDER BY event_ts DESC
LIMIT 50;

24h USD volume per pool (sign-aware, cheaper than FINAL):

SELECT pool_address,
       sumIf(toFloat64OrZero(notional_usd), sign =  1)
     - sumIf(toFloat64OrZero(notional_usd), sign = -1) AS volume_usd
FROM recipe_swaps_by_pair.fact_swaps_by_pair
WHERE chain_id = 1 AND event_ts >= now() - INTERVAL 1 DAY
GROUP BY pool_address
ORDER BY volume_usd DESC
LIMIT 25;

Postgres / MySQL:

SELECT block_number, side, source_kind, token0_address, token1_address,
       amount0, amount1, notional_usd, protocol
FROM swaps
WHERE pool_address = lower('0x88e6...')
ORDER BY block_number DESC
LIMIT 50;

Modes

Shipped defaults: ClickHouse hybrid (backfill → realtime), Postgres / MySQL historical (one-shot backfill). For live/reorg-safe ingestion use ClickHouse, see the overview.

The realtime reorg path needs a single-column UNIQUE on the position column, but position is block-level (many trades per block), so array-expanded trade rows can only carry a composite unique. Run realtime/hybrid on ClickHouse (the log table corrects reorgs per-block via the collapsing companion table); the Postgres / MySQL configs are intended for historical backfill.

Multichain

The recipe is chain-parametrized, point it at any supported EVM chain or Solana. On Solana, the same logIndex can be assigned to multiple events in one instruction, so for production the pool-fill event identity is widened with (token0_address, token1_address, token0_amount) to keep rows distinct; the trade feed it produces is identical in shape.

Fidelity gaps

price_usd / notional_usd / fee_usd are NULL when the relevant token had no in-block tokenPriceUpdate (illiquid or brand-new tokens). The recipe enriches only from same-block price updates, there is no cross-block carry-forward.
fee_* is populated for pool fills only. Aggregator-routed trades carry no per-fill fee, so fee_amount / fee_token / fee_usd are NULL on source_kind='aggregator' rows.
One trade can appear under two pool_address values when it is observed both as a direct pool fill (the pair address) and as part of an aggregator route (the aggregator address). These are distinct events with distinct vendor_event_ids by design, the two source arrays are disjoint per event.

Migrating from the REST API

This recipe supplies the volume leg of the token-pairs REST endpoint. That endpoint composes a pool registry with live numbers, so there is no 1:1 replacement, you land the pieces and join them in one view. Fidelity labels: exact = straight from the chain, calculated = derived from real DEX trades (very close, compare with a tolerance), add yourself = off-chain signal not in the feed.

GET /erc20/:token_address/pairs

Lists every DEX pool that trades a token, with price, liquidity, and 24h volume per pool. The pool list itself comes through exactly from a purpose-built registry; the live figures are computed from the same onchain trades the old endpoint uses.

This endpoint needs a combination of recipes, not one. The pool list comes from the pairAddressLookup registry recipe (pair_address_lookup_observations, one row per pool with exchange, protocol, fee, and both tokens); this recipe’s swaps table supplies the 24h volume; add Pair Reserves for liquidity, Token Prices for usd_price and the 24h change, and Token Metadata for symbols and pair_label, then join them in one view.

`/erc20/:token_address/pairs`	Data Feeds	Fidelity
`pair_address`, `exchange_address`	`pair_address_lookup_observations.*`	exact
`exchange_name`	`.protocol`	exact
`base_token`, `quote_token`	`.token0_address` / `.token1_address`	exact
`pair_label`, `token_name`, `token_symbol`, `token_decimals`	`token_metadata` / registry, derive the label	exact
`usd_price`, `usd_price_24hr*`	`token_price_updates`	calculated
`liquidity_usd`, `liquidity_skew`	`pair_reserves` × price	calculated
`volume_24h_usd`, `volume_24h_native`	`sum(swaps.notional_usd)` over 24h	calculated
`inactive_pair`	no trades in the window	calculated
`exchange_logo`, `token_logo`	add yourself	add yourself

The composed view (Postgres, query it with WHERE base_token = $1 OR quote_token = $1 ORDER BY liquidity_usd DESC NULLS LAST):

CREATE VIEW token_pairs AS
SELECT
  r.pair_address,
  r.exchange_address,
  r.protocol                     AS exchange_name,
  m0.symbol || '/' || m1.symbol  AS pair_label,
  r.token0_address               AS base_token,
  r.token1_address               AS quote_token,
  p0.usd_price,
  (res.reserve0 / power(10, r.token0_decimals) * p0.usd_price)
    + (res.reserve1 / power(10, r.token1_decimals) * p1.usd_price) AS liquidity_usd,
  v.volume_24h_usd,
  (v.volume_24h_usd IS NULL)     AS inactive_pair
FROM pair_address_lookup_observations r
LEFT JOIN token_metadata m0 ON m0.token_address = r.token0_address
LEFT JOIN token_metadata m1 ON m1.token_address = r.token1_address
LEFT JOIN pair_reserves res ON res.pair_address = r.pair_address
LEFT JOIN LATERAL (SELECT usd_price FROM token_price_updates
                   WHERE token_address = r.token0_address ORDER BY block_number DESC LIMIT 1) p0 ON true
LEFT JOIN LATERAL (SELECT usd_price FROM token_price_updates
                   WHERE token_address = r.token1_address ORDER BY block_number DESC LIMIT 1) p1 ON true
LEFT JOIN LATERAL (SELECT sum(notional_usd) AS volume_24h_usd FROM swaps
                   WHERE pool_address = r.pair_address
                     AND block_timestamp >= extract(epoch FROM now()) - 86400) v ON true;

Gotchas

A token trades in many pools (fee tiers, DEXes), list them by matching either side of the registry, and sort by liquidity to surface the meaningful ones.
Backfill is mixed: the pool list needs history far enough back to see each pool’s deployment (full history for completeness), while price, liquidity, and 24h volume only need realtime plus a 24-hour window. See History & backfill.
The volume join keys on the pair address, so it matches this recipe’s pool_fill rows only, aggregator-routed fills carry the aggregator’s address in pool_address (see Fidelity gaps).
The live figures track the old endpoint closely but come from a pricing computation, compare with a tolerance, not bit-for-bit.
exchange_logo and token_logo are off-chain images, layer them in from your own source keyed by address, or drop them.

Swaps by Token

The same trade projection, keyed by token with one row per token side.

Pair OHLCV

Roll a pair’s trades into candlesticks for charting.

Trading & Charting

The use case this per-pair trade feed powers.

Token Analytics

Pool-level volume and trade flow for token analytics.

Getting Started

Access Methods

Data APIs & Migration Guides

Streams

Gotchas & Troubleshooting

Concepts

Use Cases

Question it answers

What you get

Source

Destination

Full schema

Example reads

Modes

Multichain

Fidelity gaps

Migrating from the REST API

GET /erc20/:token_address/pairs

Swaps by Token

Pair OHLCV

Trading & Charting

Token Analytics

​Question it answers

​What you get

​Source

​Destination

​Full schema

​Example reads

​Modes

​Multichain

​Fidelity gaps

​Migrating from the REST API

​GET /erc20/:token_address/pairs

​Related

Swaps by Token

Pair OHLCV

Trading & Charting

Token Analytics

Question it answers

What you get

Source

Destination

Full schema

Example reads

Modes

Multichain

Fidelity gaps

Migrating from the REST API

GET /erc20/:token_address/pairs

Related