Robinhood Transfers Raw Sql
robinhood_transfers_raw_sqlLAST RESORT — arbitrary READ-ONLY SQL against the Robinhood transfers database. The
Bitquery MCP specialized robinhood_* tools are the PRIORITY; use this ONLY when none of them
can answer (e.g. an uncovered table). No query optimizer here — naive SQL full-scans
huge tables and JOINs time out.
Runs read-only ClickHouse SQL. SQL reference: https://clickhouse.com/docs/reference/home
FAST-QUERY RULES: filter on the indexed key tables — robinhood_api.transfers_sender (by
sender / outgoing), robinhood_api.transfers_receiver (by receiver / incoming),
robinhood_api.transfers_tx (by tx hash); NEVER JOIN big tables — use WHERE col IN (SELECT …)
(use GLOBAL IN when the subquery is referenced inside another subquery, otherwise its
result is not available where it is used). A subquery that itself reads a transfers table (IN (SELECT Transfer_Sender FROM …transfers_… WHERE …)) is re-run for every shard and rescans that table — run it as its OWN query first, then pass the result back as a literal list (IN (unhex('a'), unhex('b'))). To filter transfers by a specific token, read transfers_currency (keyed by the token contract + Block_Time); transfers_sender / transfers_receiver are keyed by address, so filtering those by a token contract scans the table.
Addresses are RAW BYTES FixedString(20) in Transfer_Sender / Transfer_Receiver
(there are NO plain string address columns) →
Transfer_Sender = unhex(substring(lower('0x…'),3)), output concat('0x',lower(hex(col))).
Tx hash is Transaction_Hash FixedString(32) (same unhex/hex pattern).
A SET of hashes or addresses must have the column's exact type:
Transaction_Hash IN (SELECT toFixedString(unhex(h), 32) FROM …), toFixedString(…, 20) for
an address. A plain unhex(…) set, or a JOIN on a String key, silently misses every value
ending in 00; = and a literal list IN (unhex('a'), unhex('b')) are safe.
Currency symbol + decimals are INLINE columns (no dictionaries):
amount = toFloat64(Transfer_Amount) / pow(10, Transfer_Currency_Decimals);
symbol = Transfer_Currency_Symbol; token contract = Transfer_Currency_SmartContract — itself RAW BYTES in a String column (not text), so filter it = unhex('<40 hex, no 0x>') and read it back concat('0x',lower(hex(…))); a '0x…' text literal matches nothing.
Always add AND Transfer_Success = 1. Transfer_Type: 'token' = a token transfer,
'transaction' = a plain native-coin send, 'call' = native coin moved by a call. A
transaction's OWN value transfer is stored either as 'transaction' or as a 'call' with
Call_Index = 0; only a 'call' with Call_Index != 0 is an INTERNAL transfer (made by
contract code deeper inside the transaction). For top-level transfers filter
(Transfer_Type IN ('token','transaction') OR (Transfer_Type = 'call' AND Call_Index = 0)) -
Transfer_Type IN ('token','transaction') alone drops a large share of ordinary sends. Time =
Block_Time. Date-time values come back as ISO-8601 UTC, e.g. 2026-09-21T15:04:18Z (toString() of a date-time gives 2026-09-21 15:04:18). Other robinhood_api.* tables (calls, transactions, balances) are reachable with
an explicit db prefix. Counterparty labels are NOT available for this chain — describe an
address from its transfer profile instead. Read-only; always add a LIMIT of at most 10,000 — a larger answer fails with an error, so aggregate (GROUP BY, count) instead. 64-bit integers come back as JSON numbers, exact only up to 2^53: return a hash such as cityHash64(...) or any other 64-bit id as toString(x).
Exact table and column names first: chain_list_tables, chain_describe_table and chain_search_columns with database=robinhood_api.
Tool call template
Replace each placeholder with a value matching the input schema. Only required parameters are included; add optional parameters as needed.
{
"id": 1,
"jsonrpc": "2.0",
"method": "tools/call",
"params": {
"arguments": {
"sql": "<sql>"
},
"name": "robinhood_transfers_raw_sql"
}
}Input Schema
| Name | Required | Description | Default |
|---|---|---|---|
| sql | Yes | A single read-only SELECT. Filter on the indexed transfers_currency (by token contract) / transfers_sender / transfers_receiver / transfers_tx tables; no JOINs over big tables. | |
| format | No | Shape of the returned data. "rows" (the default) returns one item per row. "columnar" returns a single item, {"columns":[...],"rows":[[value,...],...]}, naming the columns once and listing each row's values in that order; it is more compact for large results and carries the same values. | rows |