Index ERC-20 transfers

USDC = "0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48"
TRANSFER = "0xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef"

body = {
    "logs": [{"address": [USDC], "topic0": [TRANSFER]}],
    "fields": {"log": {"blockNumber": True, "logIndex": True, "transactionHash": True,
                       "blockTimestamp": True, "topic1": True, "topic2": True, "data": True}},
}

import pyarrow as pa
import pyarrow.parquet as pq
from bloklake_arrow import query

START, END = 19426000, 19427499          # use the token's deployment block and the head

def decode(t: pa.Table) -> pa.Table:
    """Transfer(address indexed from, address indexed to, uint256 value)."""
    return pa.table({
        "block_number": t["block_number"],
        "log_index": t["log_index"],
        "tx_hash": t["transaction_hash"],
        "from": pa.array([b[12:] for b in t["topic1"].to_pylist()], pa.binary(20)),
        "to": pa.array([b[12:] for b in t["topic2"].to_pylist()], pa.binary(20)),
        # uint256 doesn't fit any Arrow integer type: keep it as a decimal string
        "value": pa.array([str(int.from_bytes(b, "big")) for b in t["data"].to_pylist()]),
    })

writer, frm, total = None, START, 0
while frm <= END:
    tables, meta = query({**body, "fromBlock": frm, "toBlock": END})
    part = decode(tables["logs"])
    writer = writer or pq.ParquetWriter("usdc_transfers.parquet", part.schema, compression="zstd")
    writer.write_table(part)
    total += part.num_rows
    frm = int(meta["bl.next_block"])
writer.close()
print(f"{total} transfers up to block {END}")

17189 transfers up to block 19427499

import duckdb

duckdb.sql("""
    select '0x' || lower(hex("to")) as receiver, count(*) as transfers,
           sum(cast(value as hugeint)) / 1e6 as usdc
    from 'usdc_transfers.parquet'
    group by 1 order by usdc desc limit 3
""").show()