> ## Documentation Index
> Fetch the complete documentation index at: https://docs.moralis.com/llms.txt
> Use this file to discover all available pages before exploring further.
# Swaps by Token
> Sync every DEX trade that touched a token, pool fills and aggregator-routed trades, USD-enriched at block time, into your own database, keyed for fast by-token lookups.
### Question it answers
> "Give me every DEX trade that touched token **0x…**, newest first, with the amount on each side, the USD notional, the protocol, and whether it was a buy or a sell."
A single read returns what the public Moralis Swaps endpoints serve by token, stored **pre-shaped and indexed by token** in your own database. Both direct pool fills and aggregator-routed trades are captured, so you see every fill of the token once.
### What you get
The template lands **one row per (trade, token side)**, each trade is unpivoted so it appears under both of its tokens, and a by-token lookup is a prefix scan. Key columns of the fact table:
| Column | Description |
| ------------------------------------- | -------------------------------------------------------------------- |
| `token_address` | This side's token (the leg you're querying by) |
| `counter_token_address` | The other token in the trade |
| `leg` | `token0` or `token1`, which side of the underlying trade this row is |
| `side` | `buy` or `sell`, derived from the base token's pool-balance delta |
| `side_amount` | Raw amount of `token_address` (uint256, as text) |
| `counter_amount` | Raw amount of `counter_token_address` |
| `notional_usd` | Trade notional in USD (bought-leg amount × in-block price) |
| `price_usd` | Bought-leg (token1) USD price used for the notional |
| `source_kind` | `pool_fill` (direct pool) or `aggregator` (aggregator-routed) |
| `protocol` | DEX/protocol label |
| `fee_amount`, `fee_token`, `fee_usd` | Trade fee (pool fills only) |
| `trader_address`, `pool_address` | Trader and pool/aggregator address |
| `block_number`, `event_ts`, `tx_hash` | On-chain ordering and time |
### Source
The transform reads two per-block trade arrays and unions them into the trade feed:
`tokenSwaps` (direct pool fills) · `aggregateTokenSwaps` (aggregator-routed trades)
The two branches are disjoint by construction, pool fills are keyed off the pair address, aggregator routes off the aggregator address, so 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), so no separate price join is needed at read time.
### Destination
| Destination | Table | Read pattern |
| ---------------------------- | --------------------- | -------------------------------------------------------------------------------------- |
| **ClickHouse** (first-class) | `fact_swaps_by_token` | Prefix scan on `(chain_id, token_address, event_ts)`; read with `FINAL` or `sum(sign)` |
| **Postgres** | `swaps` | Indexes on `token0_address` and `token1_address`, each `(…, block_number DESC)` |
| **MySQL** | `swaps` | Indexes on `token0_address` and `token1_address` |
ClickHouse uses the collapsing log-table pattern (see the [templates overview](/data-feeds/templates/overview#destinations)) so chain reorganizations self-correct. The fact table is token-first, so all trades for a token, newest first, are a contiguous range read. Postgres/MySQL keep a flat one-row-per-trade `swaps` table and reach a token via either side's index.
### Full schema
Below is the complete read table this template produces. It's a starting point: keep the columns you need and drop the rest (see [Schema & flexibility](/data-feeds/templates/overview#schema--flexibility)). Raw `uint256` amounts and fees are stored as text in ClickHouse and as `NUMERIC(76, 0)` in Postgres (explicit precision so large raw balances never overflow); USD columns are nullable wide decimals.
```sql theme={null}
CREATE TABLE recipe_swaps_by_token.fact_swaps_by_token
(
vendor_event_id String,
ingested_at DateTime64(3),
chain_id UInt32,
block_hash String,
block_number UInt64,
event_ts DateTime64(3),
token_address String, -- this side's token (token0 or token1)
counter_token_address String,
leg LowCardinality(String), -- 'token0' | 'token1'
side_amount String, -- this token's amount (raw uint256)
counter_amount String,
trader_address String,
pool_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_token', '{replica}', sign)
PARTITION BY (chain_id, toYYYYMM(event_ts))
ORDER BY (chain_id, token_address, event_ts, vendor_event_id, leg);
```
Each trade is unpivoted into two rows (`leg = 'token0'` and `'token1'`) so it's found under both tokens. `leg` is part of the `ORDER BY` so the two sides never collapse into each other, while the `+1/−1` reorg pair for one side shares an identical key and collapses cleanly. Read with `FINAL` or `sum(sign)`, never a bare `WHERE sign = 1`. A single-node setup can use `CollapsingMergeTree(sign)` without the replication path.
```sql theme={null}
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,
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-token access (the template's primary purpose).
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);
-- Trader + block-range helpers.
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. The Postgres/MySQL table is one row per trade (not unpivoted), so a by-token read filters either `token0_address` or `token1_address`. `position` is the block-level cursor used during backfill.
### Example reads
All trades touching a token, newest first (ClickHouse):
```sql theme={null}
SELECT event_ts, leg, side, side_amount, counter_amount,
notional_usd, protocol, source_kind
FROM recipe_swaps_by_token.fact_swaps_by_token FINAL
WHERE chain_id = 1
AND token_address = lower('0xA0b86991c6218b36c1d19D4a2e9Eb0cE3606eB48')
ORDER BY event_ts DESC
LIMIT 50;
```
24h USD volume per token (sign-aware, cheaper than `FINAL`):
```sql theme={null}
SELECT token_address,
sumIf(toFloat64OrZero(notional_usd), sign = 1)
- sumIf(toFloat64OrZero(notional_usd), sign = -1) AS volume_usd
FROM recipe_swaps_by_token.fact_swaps_by_token
WHERE chain_id = 1 AND event_ts >= now() - INTERVAL 1 DAY
GROUP BY token_address
ORDER BY volume_usd DESC
LIMIT 25;
```
By-token read on Postgres / MySQL (either side matches):
```sql theme={null}
SELECT block_number, side, source_kind, amount0, amount1, notional_usd, protocol
FROM swaps
WHERE token0_address = lower('0xA0b8...') OR token1_address = lower('0xA0b8...')
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](/data-feeds/templates/overview#modes).
The realtime reorg path needs a single-column unique on the position cursor, but `position` is block-level (many trades per block), so the array-expanded trade rows can only carry a composite unique. Run **realtime/hybrid on ClickHouse**, where the collapsing log table corrects reorgs per-block; the Postgres/MySQL configs are intended for `historical` backfill.
### Multichain
The template is chain-parametrized, point it at any supported EVM chain or Solana. On Solana, multiple events in one instruction can share a `logIndex`, so the pool-fill event identity is widened (with the token addresses and amount) to keep rows distinct; the by-token feed it produces is identical in shape.
### USD valuation and fidelity gaps
* **`notional_usd`** is the bought-leg (`token1`) amount scaled by its decimals × the in-block price (`token1Amount / 10^token1Decimals × price`), so it's a true dollar value when the token had an in-block price update.
* **`price_usd`, `notional_usd`, `fee_usd`** are `NULL` when the bought token had **no in-block price update**, there's no off-block price backfill at write time. Join the Token Prices sync for fuller coverage.
* **`fee_*`** is populated for **pool fills only**; bps-style fees (V3/V4/CL) are converted to an absolute amount, and already-absolute fees pass through. Aggregator-routed rows leave fees `NULL`.
* **`side`** (`buy`/`sell`) is derived from the lower-priority (base) token's pool-balance delta on the trade.
## Migrating from the REST API
This template is the trade-level foundation for the token-analytics REST endpoint. That endpoint returns a pre-aggregated dashboard, so there is **no 1:1 replacement table**, you land the token's trades with this template and compute the rolling windows yourself, joining sibling templates for price, liquidity, and FDV. Every figure is **calculated** (aggregated from real trades, prices, reserves, and supply, very close, compare with a tolerance); there are no off-chain fields to add.
### GET /tokens/:tokenAddress/analytics
Buy/sell volume, buyers/sellers, trade counts, unique wallets, price change, liquidity, and FDV per rolling window (5m / 1h / 6h / 24h). On Data Feeds those windows become queries you own, and so does any other window or metric (buy/sell ratio, VWAP, …).
This endpoint needs a **combination of templates**, not one. Run this template (the trades) plus [Token Prices](/data-feeds/templates/token/token-prices) (`token_price_updates`, `usdPrice` and `pricePercentChange`), [Pair Reserves](/data-feeds/templates/markets/pair-reserves) (`pair_reserves`, `totalLiquidityUsd`), and [Token Metadata](/data-feeds/templates/token/token-metadata) (`token_metadata.total_supply`, FDV) against the same database, then aggregate. There is no pre-aggregated analytics template, the composition is the supported path.
| `/tokens/:tokenAddress/analytics` | Data Feeds | Fidelity |
| -------------------------------------------------------- | ---------------------------------------------------------------- | ---------- |
| `totalBuyVolume`, `totalSellVolume` (per window) | `sum(swaps.notional_usd)` by side | calculated |
| `totalBuyers`, `totalSellers`, `totalBuys`, `totalSells` | distinct traders / trade counts by side | calculated |
| `uniqueWallets` | distinct traders in the window | calculated |
| `usdPrice`, `pricePercentChange` | latest `token_price_updates.usd_price` / price now vs window-ago | calculated |
| `totalLiquidityUsd` | sum of the token's pool reserves × price | calculated |
| `totalFullyDilutedValuation` | `total_supply` × price | calculated |
The trade-side aggregation for one window (Postgres, run per window: 300 / 3600 / 21600 / 86400 seconds):
```sql theme={null}
SELECT
sum(notional_usd) FILTER (WHERE side = 'buy') AS total_buy_volume,
sum(notional_usd) FILTER (WHERE side = 'sell') AS total_sell_volume,
count(DISTINCT trader_address) FILTER (WHERE side='buy') AS total_buyers,
count(DISTINCT trader_address) FILTER (WHERE side='sell') AS total_sellers,
count(*) FILTER (WHERE side = 'buy') AS total_buys,
count(*) FILTER (WHERE side = 'sell') AS total_sells,
count(DISTINCT trader_address) AS unique_wallets
FROM swaps
WHERE (token0_address = lower($1) OR token1_address = lower($1))
AND block_timestamp >= extract(epoch FROM now()) - 86400;
```
Layer the rest on top: `usdPrice` is the latest price update, `pricePercentChange` compares it to the price a window ago, `totalLiquidityUsd` sums the token's pool reserves valued at price, and `totalFullyDilutedValuation` is `total_supply × price`.
**Gotchas**
* Backfill enough history to cover your longest window, a day or more for the 24h figures, plus price history for the price change. See [History & backfill](/data-feeds/concepts/history-and-backfill).
* Materialize per token and window on a schedule if you serve many tokens, don't recompute per request.
* Thin tokens have sparse windows, and trades whose bought leg had no in-block price carry `NULL` notional and drop out of USD sums.
* On ClickHouse, run the same aggregation over `fact_swaps_by_token` with a prefix scan on `token_address` (the unpivot means a by-token filter already sees each trade once), and make it reorg-safe with `FINAL` or sign-aware sums, never a bare `WHERE sign = 1`.
### Related
The sibling, every trade keyed by pool/pair address.
The use case this by-token trade feed powers.