native-balances-v0.2.0

What's Changed

Test with Avalanche network (no extended blocks)

• Now supports eth_get_balance to fetch Native balances for extended & no-extended blocks
• supports chunk_size as spkg params

Implements eth_get_balance:

requests.requests.push(RpcGetBalanceRequest {
    address: account.to_vec(),
    block: format!("{:#x}", block), // to hex
});

Full Changelog: clickhouse-uniswaps-v0.2.0...native-balances-v0.2.0

clickhouse-uniswaps-v0.2.0

What's Changed

• Add MVs for Native balances and transfers with fees by @0237h in #109
• SQL updates to Clickhouse EVM Uniswaps by @DenisCarriere in #108

✅ new features/fields

• Swaps add token & decimals
  • add input_token & output_tokens
  • add input_decimals & output_decimals
• Replace price field as MV input_amount / output_amount
• Define pool_activity_summary TABLE as SummingMergeTree
  • sums transactions by Pool
• Update REFRESH EVERY 1 MINUTE for ohlc_prices table (should drastically speed up ingestion time)

❌ Breaking changes

• Swaps amount0/amount1 changes
  • rename swaps.amount0 => swaps.input_amount
  • rename swaps.amount1 => swaps.output_amount
• Remove swaps.recipient field (was Null for Uniswap V4), no longer a concept in Swaps due to Aggregators doing most of the trading.
• Change Volume to Int256 instead of Float64
  • gross_volume0 & gross_volume1 to Int256
  • net_volume0 & net_volume1 to Int256
• remove name & symbol from OHLC (names & symbol change over time, best to fetch these at query time)
• remove decimals scale handling in gross_volume & prices (must be computed at Query time)

⚠️ Refactored/clean ups

• Remove extra INDEX from tables that are not being used in Token API
  • kept only idx_tx_hash TYPE bloom_filter GRANULARITY 1
• Convert ReplacingMergeTree to MergeTree for those that do not use Replacing functionality
• add global_sequence_reverse to uniswap_v3_pool_created & uniswap_v2_pair_created
  • change to ENGINE = ReplacingMergeTree(global_sequence_reverse)

Data sample for swaps

block_num:       22940200 -- 22.94 million
block_hash:      0x37e205b2fb2fc775a711a2115a664df3479c0b58c29e9ea7b2523b0c6c7ca588
timestamp:       2025-07-17 16:44:47
tx_hash:         0xd7401dfb989c81aa43ee16c01442a2ffae8cfff93cc6aade8504f68926b17e44
tx_from:         0x710d9b5c4fa0cf4c1603790e332c12188161c8b0
tx_to:           0x710d9b5c4fa0cf4c1603790e332c12188161c8b0
ordinal:         2759
caller:          0x66a9893cc07d91d95644aedd05d03f95e1dba8af
pool:            0x72331fcb696b0151904c03584b66dc8365bc63f8a144d89a773384e3a579ca73
sender:          0x66a9893cc07d91d95644aedd05d03f95e1dba8af
factory:         0x000000000004444c5dc75cb358380d2e3de08a90
input_amount:    180000000000000000
input_token:     0x0000000000000000000000000000000000000000
input_decimals:  18
output_amount:   610885871 -- 610.89 million
output_token:    0xdac17f958d2ee523a2206206994597c13d831ec7
output_decimals: 6
protocol:        uniswap_v4

SQL Examples

OHLC for WETH/USDT

-- OHLC Prices by Pool --
WITH (
      pow(10, decimals0) AS scale0,
      pow(10, decimals1) AS scale1,
      pow(10, decimals0 - decimals1) AS scale,
      2 AS precision -- user defined
) SELECT
      timestamp,
      'WETH/USDT' AS ticker,

      -- OHLC --
      floor(argMinMerge(open0) * scale, precision)                        AS open,
      floor(quantileDeterministicMerge(0.99)(quantile0) * scale, precision)   AS high,
      floor(quantileDeterministicMerge(0.01)(quantile0) * scale, precision)    AS low,
      floor(argMaxMerge(close0) * scale, precision)                       AS close,

      -- volume --
      floor(sum(gross_volume0) / scale0, precision)         AS "gross volume (ETH)",
      floor(sum(gross_volume1) / scale1, precision)         AS "gross volume (USD)",
      floor(sum(net_flow0) / scale0, precision)             AS "net flow (ETH)",
      floor(sum(net_flow1) / scale1, precision)             AS "net flow (USD)",

      -- universal --
      uniqMerge(uaw)          AS uaw,
      sum(transactions)       AS transactions
FROM ohlc_prices
WHERE pool = lower('0x72331fcb696b0151904c03584b66dc8365bc63f8a144d89a773384e3a579ca73') -- Uniswap V4 WETH/USDT
GROUP BY pool, timestamp, decimals0, decimals1
ORDER BY timestamp DESC
LIMIT 10;

    ┌───────────timestamp─┬─ticker────┬────open─┬────high─┬─────low─┬───close─┬─gross volume (ETH)─┬─gross volume (USD)─┬─net flow (ETH)─┬─net flow (USD)─┬─uaw─┬─transactions─┐
 1. │ 2025-07-18 02:00:00 │ WETH/USDT │ 3600.64 │ 3625.39 │ 3589.55 │ 3600.64 │           14433.08 │        52045358.12 │         924.26 │    -3307896.42 │ 127 │         1629 │
 2. │ 2025-07-18 01:00:00 │ WETH/USDT │  3563.6 │ 3618.31 │ 3531.76 │  3563.6 │           35552.46 │        127502677.5 │       -4164.95 │    14961626.01 │ 181 │         2637 │
 3. │ 2025-07-18 00:00:00 │ WETH/USDT │ 3512.17 │ 3539.28 │ 3463.27 │ 3512.17 │           12772.77 │        44672527.91 │       -3268.82 │    11483158.12 │ 112 │         1611 │
 4. │ 2025-07-17 23:00:00 │ WETH/USDT │  3507.7 │ 3518.12 │ 3466.23 │  3507.7 │           18207.78 │        63614495.83 │        -589.03 │      2075611.3 │ 133 │         1818 │
 5. │ 2025-07-17 22:00:00 │ WETH/USDT │ 3478.37 │  3491.4 │ 3455.54 │ 3478.37 │           10969.87 │        38123439.82 │         122.29 │     -403755.31 │  90 │         1278 │
 6. │ 2025-07-17 21:00:00 │ WETH/USDT │ 3494.54 │ 3498.73 │ 3419.21 │ 3494.54 │           20946.85 │        72624773.36 │       -2790.91 │     9644798.85 │ 136 │         1953 │
 7. │ 2025-07-17 20:00:00 │ WETH/USDT │ 3399.23 │ 3416.22 │ 3376.61 │ 3399.23 │           15771.03 │        53590941.46 │        -516.43 │     1789684.85 │ 113 │         1557 │
 8. │ 2025-07-17 19:00:00 │ WETH/USDT │  3443.6 │ 3451.41 │ 3408.28 │  3443.6 │            7297.09 │        25053358.88 │        1036.75 │    -3532523.34 │  79 │          981 │
 9. │ 2025-07-17 18:00:00 │ WETH/USDT │ 3432.33 │ 3440.73 │ 3417.44 │ 3432.33 │            6817.15 │        23370140.82 │        -227.76 │      792040.12 │  91 │         1115 │
10. │ 2025-07-17 17:00:00 │ WETH/USDT │ 3400.53 │  3424.8 │ 3390.55 │ 3400.53 │            8113.13 │        27627427.65 │       -2174.46 │     7426578.68 │  83 │         1110 │
    └─────────────────────┴───────────┴─────────┴─────────┴─────────┴─────────┴────────────────────┴────────────────────┴────────────────┴────────────────┴─────┴──────────────┘

Full Changelog: clickhouse-tokens-v1.16.0...clickhouse-uniswaps-v0.2.0

Search top pools by total transactions

SELECT
    pool,
    token0,
    token1,
    transactions
FROM pool_activity_summary
WHERE token0 != ''
ORDER BY transactions DESC
LIMIT 10

Query id: 3bc4e3b5-8fdd-4929-8042-f1fffbaf67aa

    ┌─pool───────────────────────────────────────────────────────────────┬─token0─────────────────────────────────────┬─token1─────────────────────────────────────┬─transactions─┐
 1. │ 0x88e6a0c2ddd26feeb64f039a2c41296fcb3f5640                         │ 0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48 │ 0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2 │       112294 │
 2. │ 0x72331fcb696b0151904c03584b66dc8365bc63f8a144d89a773384e3a579ca73 │ 0x0000000000000000000000000000000000000000 │ 0xdac17f958d2ee523a2206206994597c13d831ec7 │        78700 │
 3. │ 0x4d63da0421ab1d71fbf2a3d3c8625a66d9b9799d                         │ 0xdac17f958d2ee523a2206206994597c13d831ec7 │ 0xe2ad0bf751834f2fbdc62a41014f84d67ca1de2a │        62228 │
 4. │ 0x0d4a11d5eeaac28ec3f61d100daf4d40471f1852                         │ 0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2 │ 0xdac17f958d2ee523a2206206994597c13d831ec7 │        43078 │
 5. │ 0xb570b29573fe661ccf607ba47d91b737148d8289                         │ 0x06168a340096872bbf5061e5ebad84a7f172bb00 │ 0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2 │        35326 │
 6. │ 0x9eed63e14f2cceb1434546005b4445049b41a6ac                         │ 0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2 │ 0xc7eae11e9e03500461a0b9169addac8ae2f0e37d │        30324 │
 7. │ 0x25bda6e622a477ca702902fab161ace70839994b                         │ 0x698acb976e0c07078b06cdc5093f7cdaf55d94d6 │ 0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2 │        29446 │
 8. │ 0x10c4e72abd373295e613e3d2c2c5067d33a0e4a8                         │ 0x6055dc6ff1077eebe5e6d2ba1a1f53d7ef8430de │ 0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2 │        26921 │
 9. │ 0xcc7fa2fa60b745938197b834001780efb89e03a4                         │ 0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2 │ 0xfed9d09e347af985a055a4f5cd2d8a30a7d692cc │        24995 │
10. │ 0x2e3b8bcb8da0b96a5f845705fa7510f4d5f63f28                         │ 0x22fb1d972e2d37f5e8ff6ff3655aa225e3485de1 │ 0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2 │        24683 │
    └────────────────────────────────────────────────────────────────────┴────────────────────────────────────────────┴────────────────────────────────────────────┴──────────────┘

Search Swaps by Pool

WITH (
    pow(10, input_decimals) AS scale0,
    pow(10, output_decimals) AS scale1,
    pow(10, input_decimals - output_decimals) AS scale
)
SELECT
    input_token as input_token,
    input_amount / scale0 AS input_amount,
    input_decimals,
    output_token as output_token,
    output_decimals,
    output_amount / scale1 AS output_amount,
    price * scale AS price
FROM swaps
WHERE pool = '0x72331fcb696b0151904c03584b66dc8365bc63f8a144d89a773384e3a579ca73'
LIMIT 20;
    ┌─input_token────────────────────────────────┬────────input_amount─┬─input_decimals─┬─output_token───────────────────────────────┬─output_decimals─┬─output_amount─┬──────────────price─┐
 1. │ 0x0000000000000000000000000000000000000000 │            0.463605 │             18 │ 0xdac17f958d2ee523a2206206994597c13d831ec7 │               6 │   1669.230228 │  3600.544057980393 │
 2. │ 0x0000000000000000000000000000000000000000 │             0.01575 │             18 │ 0xdac17f958d2ee523a2206206994597c13d831ec7 │               6 │     56.722634 │ 3601.4370793650796 │
 3. │ 0x0000000000000000000000000000000000000000 │   4.342606455440937 │            ...

erc20-transfers-v0.2.1

• Add log_index field
• Update erc20-stores which depends on erc20-transfers spkg

erc20-metadata-v0.2.1

What's Changed

• Remove duplicate eth_call by @YaroShkvorets in #103
• Dissolve erc20-metadata-functions into erc20-metadata
• Update to latest [email protected] (previous caused memory issues)

Full Changelog: clickhouse-nfts-v0.6.2...erc20-metadata-v0.1.2

clickhouse-tokens-v1.16.0

Updates

• add log_index to transfers & erc20_transfers
• fix SQL: Incorrect amount in transfers table #92
• Remove duplicate eth_call by @YaroShkvorets in #103
• Remove erc20_metadata_functions module (events have been incorporated in erc20_metadata)
• Update erc20_stores with latest erc20-transfers package (will require re-processing stores)

New *.spkg versions

• https://github.com/pinax-network/substreams-evm-tokens/releases/tag/erc20-transfers-v0.2.1
• https://github.com/pinax-network/substreams-evm-tokens/releases/tag/erc20-metadata-v0.2.1

Full Changelog: erc20-transfers-v0.2.1...clickhouse-tokens-v1.15.1

clickhouse-nfts-v0.6.2

• Changes: 7dfb341
• Bumped from v0.6.1

clickhouse-nfts-v0.6.1

Changes

• Alter punk_index to use UInt256 instead of UInt64

Alter SQL table

No change in Substreams, only SQL table structure

ALTER TABLE punk_assigns MODIFY COLUMN punk_index UInt256;
ALTER TABLE punk_transfers MODIFY COLUMN punk_index UInt256;
ALTER TABLE punk_bought MODIFY COLUMN punk_index UInt256;
ALTER TABLE punk_bid_entered MODIFY COLUMN punk_index UInt256;
ALTER TABLE punk_bid_withdrawn MODIFY COLUMN punk_index UInt256;
ALTER TABLE punk_no_longer_for_sale MODIFY COLUMN punk_index UInt256;
ALTER TABLE punk_offered MODIFY COLUMN punk_index UInt256;

What's Changed

• Update UInt256 by @DenisCarriere in #100

Full Changelog: clickhouse-nfts-v0.6.0...clickhouse-nfts-v0.6.1

clickhouse-nfts-v0.6.0

What's Changed

• fix: handle mismatched ids and values lengths in ERC1155 batch transfers by @YaroShkvorets in #90
• Implement ERC721 crypto punks by @DenisCarriere in #98
• includes v0.6.1 SQL schema changes https://github.com/pinax-network/substreams-evm-tokens/releases/tag/clickhouse-nfts-v0.6.1

ERC721 new data

CryptoPunks events have also been included in ERC721 tables & metadata

New CryptoPunks TABLES

• punk_assigns
• punk_transfers
• punk_bought
• punk_bid_entered
• punk_bid_withdrawn
• punk_no_longer_for_sale
• punk_offered

Notes 📔

• CryptoPunks include their own marketplace for NFT sales, however this release does not include these sales in OHLC.
• For NFT sales, reference punk_bought / punk_bid_entered TABLES
• Some punk_bought events contain Null value, refer to value_is_null field
• punk_assigns is equivalent as a Mint event

Full Changelog: clickhouse-tokens-v1.15.0...clickhouse-nfts-v0.6.0

tokens-v0.2.0

Fixes #84

WETH (version 9) only includes Deposit & Withdraw events, does not include a Transfer event

This can cause the user to:

• Redeem WETH via withdraw
• ✅ Update Native Balances
• ❌ NOT update ERC-20 balance (remains the same)

Substreams Packages updated

• erc20-transfers
• erc20-supply (depends on erc20-transfers)
• erc20-balances-rpc (depends on erc20-transfers)

clickhouse-tokens-v1.15.0

• Fixes #84 (WETH9 Deposits/Withdrawls)
• Updates packages from erc20-tokens-v0.2.0
  https://github.com/pinax-network/substreams-evm-tokens/releases/tag/tokens-v0.2.0