Token Analytics

​

Who it’s for

​

The recipe bundle

​

How the pieces fit

Token Transfers ──┬──►  Token Stats          (per-token transfer counters)
                  ├──►  Token Balances by Token  (latest balance per wallet)
                  └──►  Token Holders         (balances + in-block USD value)

Swaps by Token  ──────►  trade + volume feed  (every DEX fill, USD-enriched)

• Token Transfers is the activity backbone: every movement of the token, one row each. It carries each side’s absolute post-transfer balance, which is what makes the balance and holder views a latest-observation lookup rather than a running sum.
• Token Stats folds the transfer stream into a per-token counter row: the cheap, always-current “how many transfers” answer, and the pattern to extend with any future per-token metric.
• Token Balances by Token and Token Holders project the same transfers into the current balance per (token, wallet); Token Holders adds the in-block USD spot price and best-effort usd_value.
• Swaps by Token is the market leg: trading activity and USD volume, separate from raw transfers, so you can split “moved” from “traded.”

​

Distribution + activity in one read

SELECT wallet_address,
       argMax(balance, (block_number, log_index))   AS current_balance,
       argMax(usd_price, (block_number, log_index)) AS usd_price
FROM   recipe_token_holders.fact_token_holders FINAL
WHERE  chain_id = 1 AND token_address = lower('0xA0b8...')
GROUP BY wallet_address
HAVING current_balance != '0' AND current_balance != ''
ORDER BY toFloat64OrZero(current_balance) DESC
LIMIT 100;

SELECT sum(transfers_total) AS transfers_total
FROM   recipe_token_stats.fact_token_stats
WHERE  chain_id = 1 AND token_address = lower('0xA0b8...');

SELECT 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  token_address = lower('0xA0b8...')
  AND  event_ts >= now() - INTERVAL 1 DAY;

​

Notes and considerations

• Read canonical state correctly. On ClickHouse, read with FINAL or a sign-aware aggregate (sum, sumIf … sign), never a bare WHERE sign = 1. The collapsing/summing log tables converge on canonical state on merge; a bare sign filter sees uncollapsed reorg rows.
• Balances are observations, not deltas. Token Holders and Token Balances by Token rely on the absolute fromPostBalance / toPostBalance carried on each transfer, so the current holding is just the latest observation per (token, wallet): no reconstruction. The EVM zero address (mint/burn counterparty) is skipped.
• Decimals and USD. Token Holders’ usd_value is a best-effort in-block mark using a fixed scale; for precise reporting, scale raw amounts by 10^token_decimals from a Token Metadata sync and value against a Token Prices feed.
• EVM + Solana. These recipes are chain-parametrized; set the Solana chain env vars to run the same shapes against Solana, where the event ids stay row-unique despite repeated logIndex within an instruction.
• Run mode and scale. Run the ClickHouse path in hybrid mode to backfill history and then tail the chain head, reorg-safe. A high-volume token’s transfer and swap tables are large; scope chain-wide reads to a block or time range, and lean on the token-first sort key for prefix scans.
• Pair with other recipes. Add Token Prices for precise valuation and price history, and Token Metadata for decimals, name, and symbol on your dashboard.

​

Get started