Robinhood Chain 数据集分析师实战手册（SQL Cookbook）

-- 错误写法（同名别名）：
SELECT hex(from) AS from FROM {db}.transactions WHERE from = unhex('...');

SELECT
    intDiv(number, 100) * 100 AS block_window_start,
    count() AS block_count,
    min(timestamp) AS min_time,
    max(timestamp) AS max_time,
    round(avg(tx_count), 2) AS avg_txs_per_block,
    intDiv(sum(gas_used), toUInt64(count())) AS avg_gas_used,
    max(gas_used) AS max_gas_used,
    formatReadableQuantity(sum(gas_used)) AS total_gas_used_readable
FROM robinhood.blocks FINAL
WHERE is_deleted = 0 AND number >= 72040000 AND number < 72040500
GROUP BY block_window_start
ORDER BY block_window_start
SETTINGS max_threads=2, max_execution_time=60

72040000	100	2026-09-25 07:13:59	2026-09-25 07:14:09	6.45	897553	3783508	89.76 million
72040100	100	2026-09-25 07:14:09	2026-09-25 07:14:19	8.96	1326237	8514856	132.62 million
72040200	100	2026-09-25 07:14:19	2026-09-25 07:14:29	5.09	883732	5303836	88.37 million
72040300	100	2026-09-25 07:14:30	2026-09-25 07:14:40	5.52	849874	7510922	84.99 million
72040400	100	2026-09-25 07:14:40	2026-09-25 07:14:50	4.74	662921	5525806	66.29 million

SELECT
    period_start,
    blocks,
    txs AS user_txs,
    txs_system,
    tps,
    round(user_tx_share * 100, 2) AS user_tx_pct,
    avg_block_interval_secs,
    p50_block_interval_secs
FROM robinhood.throughput_minute FINAL
ORDER BY period_start DESC
LIMIT 5

2026-09-25 07:15:00	406	2033	406	33.883	83.35	0.10344827586206896	0
2026-09-25 07:14:00	590	2919	590	48.65	83.19	0.1016949152542373	0
2026-09-25 07:13:00	590	2913	590	48.55	83.16	0.1016949152542373	0
2026-09-25 07:12:00	414	2151	424	35.85	83.53	0.09685230024213075	0
1998-07-09 16:00:00	4	4	0	0.067	100	-222580134.5	1

SELECT
    day,
    blocks,
    txs,
    failed_txs,
    round(failed_txs / txs * 100, 2) AS failure_rate_pct,
    formatReadableQuantity(gas_used_sum) AS total_gas_used_readable,
    uniqExactMerge(unique_senders) AS unique_senders_count
FROM robinhood.daily_chain_stats FINAL
GROUP BY day, blocks, txs, failed_txs, gas_used_sum
ORDER BY day DESC

2026-09-25	2000	12026	1150	9.56	2.00 billion	3892
1998-07-09	4	4	0	0	84.00 thousand	1

SELECT
    d.date AS report_date,
    uniqExactMerge(d.active_state) AS dau_count,
    n.new_addresses_count
FROM robinhood.daily_active_addresses AS d FINAL
LEFT JOIN (
    SELECT first_seen_date, count() AS new_addresses_count
    FROM robinhood.address_first_seen FINAL
    GROUP BY first_seen_date
) AS n ON d.date = n.first_seen_date
GROUP BY d.date, d.active_state, n.new_addresses_count
ORDER BY d.date DESC

2026-09-25	3891	3891
1998-07-09	1	1

SELECT
    hex(assumeNotNull(to)) AS contract_address_hex,
    count() AS failed_tx_count,
    sum(gas_used) AS total_wasted_gas,
    round(avg(gas_used), 0) AS avg_wasted_gas
FROM robinhood.transactions FINAL
WHERE is_deleted = 0
  AND status = 0
  AND to IS NOT NULL
GROUP BY to
ORDER BY failed_tx_count DESC
LIMIT 5

F3B19FED4466C27D8CAFD9D4B81595609C1CC711	291	10500670	36085
C33ACC93942105C57602E59E8E448C20BA718A85	94	3442274	36620
520ED467860D840EC6CA20F1DACB9C0FA5168AA7	57	2588279	45408
490DDE6CE6D74213CFE0CC3A8C6498D0CD67B991	47	1886221	40132
90A4019E8A04A8A81AF61EAE803DC5885A49B6CE	44	1569081	35661

SELECT
    hex(substring(t.input, 1, 4)) AS method_id_hex,
    if(m.name IS NULL OR m.name = '', 'Unknown / Custom', m.name) AS method_name,
    if(m.signature IS NULL OR m.signature = '', 'N/A', m.signature) AS method_signature,
    count() AS call_count
FROM robinhood.transactions AS t FINAL
LEFT JOIN robinhood.method_selectors AS m FINAL
  ON substring(t.input, 1, 4) = m.selector
WHERE t.is_deleted = 0
  AND length(t.input) >= 4
GROUP BY method_id_hex, m.name, m.signature
ORDER BY call_count DESC
LIMIT 5

6BF6A42D	Unknown / Custom	N/A	2000
3593564C	execute	execute(bytes,bytes[],uint256)	1382
095EA7B3	approve	approve(address,uint256)	900
04E45AAF	Unknown / Custom	N/A	300
5B70EA9F	Unknown / Custom	N/A	298

SELECT
    block_number,
    hex(hash) AS tx_hash_hex,
    hex(from) AS from_hex,
    hex(assumeNotNull(to)) AS to_hex,
    toString(value) AS value_wei_str,
    status,
    gas_used
FROM robinhood.transactions FINAL
WHERE is_deleted = 0
  AND block_number >= 72040000 AND block_number < 72040500
  AND value > 0
ORDER BY value DESC
LIMIT 5
SETTINGS max_threads=2, max_execution_time=60

72040327	554777FCBE11DC885BD17015EA98304B1D10E5960899A4F567232D84C5AFB6E8	504606DBD92873F283A6A73DCD5CC095B63A2692	89E5DB8B5AA49AA85AC63F691524311AEB649EBA	90090000000000000000	1	2734126
72040317	648E60FBA736A32FCEB7122875649C76448613F8B60D0DD64D56C54893D8258B	9600C223A94953E10204B584F309E5FEC5609838	E34A1D5ECFB4EE0064C3E3DEE9B20A722D5EC1EE	10200000000000000000	1	7394115
72040325	31FC96392B1E23DC089BC85999E2A8F71E75899F82D96512D72AAC05E226DBE0	38304ACC79F5A5E32C76BCCF6ADF65BA4CACA265	4EFB8244713D773A9518F05DC1D823EC0F336143	6843536269511544245	1	21000
72040221	DCC046F6BA14BFA032CFB271895057049B85E7B803241B8611C8720615760576	251E39FB07D89C3A7C5F75ED82304F5EEB15E4F9	0000000000000000000000000000000000000000	5090000000000000000	1	545144
72040440	959C2EA1E77573520A3217977B782F5F6E20F8DC61A83FB2709AE367807A768C	4EFB8244713D773A9518F05DC1D823EC0F336143	1CBAF24D53FE930FCE8EFF149FA797D2611DA149	4229376025387725515	1	5177147

SELECT
    date,
    tx_count,
    toString(total_fee_wei) AS total_fee_wei_str,
    toString(total_l1_fee_wei) AS total_l1_fee_wei_str,
    toString(total_l2_fee_wei) AS total_l2_fee_wei_str,
    round(l1_share * 100, 2) AS l1_cost_pct,
    toString(median_effective_gas_price) AS median_gas_price_wei
FROM robinhood.daily_fee_stats FINAL
ORDER BY date DESC

2026-09-25	12026	77913208315796000	560945222442000	77352263093354000	0.72	38834000
1998-07-09	4	0	0	0	nan	0

SELECT
    date,
    hex(from) AS sender_address_hex,
    tx_count,
    toString(total_fee_wei) AS total_fee_wei_str
FROM robinhood.daily_fee_by_address FINAL
ORDER BY total_fee_wei DESC
LIMIT 5

2026-09-25	759D0B33AF8BC5BA0FA5EA78E9BDDD19EF65A976	147	1150151252388000
2026-09-25	A80FDB561A70122B7AD35C92362D4A78BF0DDCA9	2	1018139733856000
2026-09-25	69374F8233A4069D48F8994D84052ECAE6277EF5	12	759279084546000
2026-09-25	49BBF2B70955FB3A106E084D4BFDA92D334573D2	106	746673489134000
2026-09-25	CA7DED7E4F4BA8AB3B10009236AE6D1B95094589	14	733016717708000

SELECT
    block_number,
    hex(tx_hash) AS tx_hash_hex,
    hex(from) AS from_hex,
    hex(to) AS to_hex,
    toString(amount) AS raw_amount_str
FROM robinhood.erc20_transfers FINAL
WHERE is_deleted = 0
  AND token = unhex('322f0929c4625ed5bad873c95208d54e1c003b2d')
ORDER BY block_number DESC, log_index DESC
LIMIT 5
SETTINGS max_threads=2, max_execution_time=60

72178334	073086B404882EFAF323EC8F9CC6F49E6FD736C31AC4F8178989E559569E81D9	583F0838CA0254470F293EE128E5C5FC202346AC	352771B68DA27E3BECE96EB2C8532D7FAED1DE1B	313975280110943158
72178334	073086B404882EFAF323EC8F9CC6F49E6FD736C31AC4F8178989E559569E81D9	C4F0172D6AC8DD294DD1137D047D5E1893760236	583F0838CA0254470F293EE128E5C5FC202346AC	313975280110943158
72178287	F2E362CB9AEB3A257223E9B7274E7C65638FAD29A4DDDB6E0C78B86600C79011	1521027B665FA38FA4A642991607AD708376DD7B	8366A39CC670B4001A1121B8F6A443A643E40951	591019880048593450
72178287	F2E362CB9AEB3A257223E9B7274E7C65638FAD29A4DDDB6E0C78B86600C79011	2F4579CA81717D3D61BF8B6F06571877BBE54A07	1521027B665FA38FA4A642991607AD708376DD7B	591019880048593450
72178279	752FDB208FDC8AE6CE5604378A246A6C0C4392114F4907E90EEDA0842FC9BB18	1589327304FECBEB57E035D5CA0BC77AE48B29FA	8366A39CC670B4001A1121B8F6A443A643E40951	816977463322781619

SELECT
    hex(b.holder) AS holder_address_hex,
    toString(b.balance) AS balance_wei_str,
    multiIf(
        tk.decimals = 18,
          divideDecimal(toDecimal256(b.balance, 18),
                        toDecimal256(toUInt256(concat('1', repeat('0', toUInt32(18)))), 18)),
        tk.decimals = 6,
          divideDecimal(toDecimal256(b.balance, 6),
                        toDecimal256(toUInt256(concat('1', repeat('0', toUInt32(6)))), 6)),
        toDecimal256(b.balance, 0)
    ) AS formatted_balance,
    tk.symbol
FROM robinhood.erc20_balances AS b
LEFT JOIN robinhood.tokens AS tk FINAL ON tk.address = b.token
WHERE b.token = unhex('0bd7d308f8e1639fab988df18a8011f41eacad73')
  AND b.holder != unhex('0000000000000000000000000000000000000000')
  AND b.balance > 0
ORDER BY b.balance DESC
LIMIT 5

2C56FB7AEBC0FC4DB0D8213E0A0FA712429C3E5A	90236602999286000000	90.236602999286	WETH
7DEC8663BC55F3419A130FAC7A7209B959ED8D90	27185866126536174335	27.185866126536174335	WETH
D2FF78DD27B1DF44FB69E76E1F7502C05A3CF258	21584112343026354113	21.584112343026354113	WETH
A70FC67C9F69DA90B63A0E4C05D229954574E313	18528247652010464263	18.528247652010464263	WETH
8366A39CC670B4001A1121B8F6A443A643E40951	10773313795014629015	10.773313795014629015	WETH

SELECT
    hex(token) AS token_hex,
    sumIf(balance, holder != unhex('0000000000000000000000000000000000000000')) AS total_holder_balance,
    -sumIf(balance, holder = unhex('0000000000000000000000000000000000000000')) AS net_minted_from_zero,
    total_holder_balance = net_minted_from_zero AS is_invariant_conserved
FROM robinhood.erc20_balances
WHERE token = unhex('0bd7d308f8e1639fab988df18a8011f41eacad73')
GROUP BY token

0BD7D308F8E1639FAB988DF18A8011F41EACAD73	128040347348517530551	128040347348517530551	1

SELECT
    s.day,
    hex(s.token) AS token_hex,
    coalesce(tk.symbol, 'Unknown') AS symbol,
    s.transfers AS transfer_count,
    uniqExactMerge(s.unique_senders) AS unique_senders,
    uniqExactMerge(s.unique_receivers) AS unique_receivers
FROM robinhood.daily_token_stats AS s FINAL
LEFT JOIN robinhood.tokens AS tk FINAL ON tk.address = s.token
GROUP BY s.day, s.token, tk.symbol, s.transfers, s.unique_senders, s.unique_receivers
ORDER BY s.transfers DESC
LIMIT 5

2026-09-25	0BD7D308F8E1639FAB988DF18A8011F41EACAD73	WETH	5390	327	398
2026-09-25	5FC5360D0400A0FD4F2AF552ADD042D716F1D168	USDG	4489	511	522
2026-09-25	D885F14B6715DA7D91E10AAA7110254BA8E4A0E7	VIRTUAL	4003	4	4003
2026-09-25	C0D6457C16CC70D6790DD43521C899C87CE02F35	META	413	35	43
2026-09-25	7FBE55D3284889EC9FA026536444ABE7741A898A	Unknown	357	107	144

SELECT
    symbol,
    name,
    hex(address) AS token_address_hex,
    hex(factory) AS factory_hex,
    created_block,
    hex(created_tx_hash) AS created_tx_hex,
    confidence
FROM robinhood.stock_tokens FINAL
ORDER BY symbol

（0 行：本地样本库 stock_tokens 尚未补数；全链补数后每个已注册股票代币返回一行）

SELECT
    a.day,
    st.symbol,
    a.mint_count,
    toString(a.mint_volume) AS mint_volume_wei,
    a.burn_count,
    toString(a.burn_volume) AS burn_volume_wei,
    a.transfer_count,
    a.unique_holders
FROM robinhood.stock_token_daily_activity AS a FINAL
JOIN robinhood.stock_tokens AS st FINAL ON st.address = a.token
ORDER BY a.transfer_count DESC

2026-09-25	META	0	0	1	51279234350000000000	413	43
2026-09-25	TSLA	0	0	0	0	216	31
2026-09-25	NVDA	0	0	1	149082437380000000000	95	46
2026-09-25	AAPL	0	0	0	0	25	15
2026-09-25	GOOGL	0	0	0	0	21	16

SELECT
    hex(pool_key) AS pool_key_hex,
    protocol,
    hex(token0) AS token0_hex,
    hex(token1) AS token1_hex,
    fee,
    block_number
FROM robinhood.dex_pools FINAL
ORDER BY block_number DESC
LIMIT 5

02F04E5D36F667BC089FFD24DC0E4C2497C1F19EDDD25197B6C2A43F3A7C376B	uniswap_v4	0000000000000000000000000000000000000000	650BF9154EDFB41AD65E316BE31FE489CCC76EB0	2500	72040869
67E083673CBDB4E26FA9D3B386CA446A3622D236CC3E760B1C134CC9DEF14644	uniswap_v4	0BD7D308F8E1639FAB988DF18A8011F41EACAD73	9C10D5D542A7FFCF47D5C7900F152825247C0A3B	8388608	72040792
978792B8E2F01CB74DFC4CEC5AEE602415A34DABB069F7730BDB31E090A56413	uniswap_v4	0000000000000000000000000000000000000000	D34EA74F01342C02A09368D2E563A34D3EE6CC28	810000	72040478
0F5B6757C733F00D7D65B7820CDBA0401D478134B3898F57D2D8AC9B0D0B981D	uniswap_v4	0000000000000000000000000000000000000000	314B0165FF44F08B821519AFE7070C4C4C8D2D9E	10000	72040451
0000000000000000000000002C56FB7AEBC0FC4DB0D8213E0A0FA712429C3E5A	uniswap_v2	0BD7D308F8E1639FAB988DF18A8011F41EACAD73	AE7A4BA92623D4513D0EF71D229D3DA9BC7AF073	0	72040327

SELECT
    block_number,
    hex(tx_hash) AS tx_hash_hex,
    hex(pool_key) AS pool_key_hex,
    protocol,
    hex(sender) AS sender_hex,
    toString(amount0) AS amount0_str,
    toString(amount1) AS amount1_str
FROM robinhood.dex_swaps FINAL
ORDER BY block_number DESC, log_index DESC
LIMIT 5

72040999	88B70F3FEC3DEB870DE778E55503903F9BE64B06D8332124324292ACD669E531	2C9D6DF7DFBE132DE209E61C295860413BB43EC869726B1E7751B9561F34E753	uniswap_v4	8876789976DECBFCBBBE364623C63652DB8C0904	-220000000000000000	7056632526207472959300634
72040998	5CD6581047439A49FE9E97CA4739695A7248102CCF8386A3CDA7EC9B607A4FF3	0000000000000000000000003319CAF98A5C6C947CB7E6EC6D45D0A8CA090668	uniswap_v3	2A7F3D7486641C77600B9B9256132755C8AEBB4F	29734878	-2801756948188204535782
72040997	F90C96A239BFAF992200EB526927FB000171BA81D9BDD0812ADC05D049597760	9ED2C8C087CA6659DFCFE03D514C8938D4D751ACBDC812D4ECD1481EA426F1BD	uniswap_v4	8F10B468B06C6FD214B65F87778827F7D113F996	1354996448940534465064690	-656202083211172810
72040996	3AC4FE75D839D00FC69996EB42FD3CEFAF42BA18691DCCE13F6EBAF83E2A0D73	6AFCABAFA7BEC957568DE2C9265E031E1DEDE25A2B0941FCAB51BC4762AC9A6D	uniswap_v4	E03952268A04AFE16CC1B02C612478BD54D6B7A6	180352672328829853	-25314009531000000000000000
72040996	2A2E9CFAF166BA90C514F5CF75939810AAD76583BF0E8C469AEA99E697DC231E	93CC54EB4E1928E841CB8B2E488BD82E8D97D56E0AAB16CF078EF71C576DCDED	uniswap_v4	BD665831A520182E944AF92B23A317791C5D8EFA	-103819250	685138393258481682051238

SELECT
    hex(s.pool_key) AS pool_key_hex,
    s.protocol,
    count() AS swap_count,
    uniqExact(s.sender) AS unique_traders
FROM robinhood.dex_swaps AS s FINAL
GROUP BY s.pool_key, s.protocol
ORDER BY swap_count DESC
LIMIT 5

00000000000000000000000052E65B17FB6E5BA00ED806F37AFCD2DAA50271CA	uniswap_v3	536	43
8B0C0CA8C3B45B8F377C7D66A5778B4431FC3AE45390D6E5DFC3B48E482F59C4	uniswap_v4	317	4
0000000000000000000000001F46899AA5BF5911CABEE5B911C450FD709A09E7	uniswap_v3	250	2
99F1143EF8323CC044F6DD8B521D5FE871ABD4E2FB141510D35E955111C05D91	uniswap_v4	177	4
6AFCABAFA7BEC957568DE2C9265E031E1DEDE25A2B0941FCAB51BC4762AC9A6D	uniswap_v4	134	1

SELECT
    block_number,
    hex(token) AS token_hex,
    toString(token_id) AS token_id_str,
    hex(from) AS from_hex,
    hex(to) AS to_hex,
    hex(tx_hash) AS tx_hash_hex
FROM robinhood.erc721_transfers FINAL
WHERE is_deleted = 0
ORDER BY block_number DESC, log_index DESC
LIMIT 5
SETTINGS max_threads=2, max_execution_time=60

72178337	07F44C47743A2F36414A82B9F558ECFCF0EEDCEF	190332	C14BDC429EFBD882AC7CBDF2E36CDEA9FC3941C9	BA618977FE27A4D86663867D258491E953644DA1	CAFCCF62E0F167F945B1947813F989CD5692C974FBD88D15147F3DE384C503F7
72178332	07F44C47743A2F36414A82B9F558ECFCF0EEDCEF	190445	BA003DE9B3F1C5D448E15EC7B637AD8EC943B730	FBB3F4BEF58373FF4C9D72AE309FEB76B3F985D2	9C4FF725A2C9F1167101139B4ED62926FD5713F77E70748568995058C86E421A
72178332	07F44C47743A2F36414A82B9F558ECFCF0EEDCEF	190445	0000000000000000000000000000000000000000	BA003DE9B3F1C5D448E15EC7B637AD8EC943B730	9C4FF725A2C9F1167101139B4ED62926FD5713F77E70748568995058C86E421A
72178332	07F44C47743A2F36414A82B9F558ECFCF0EEDCEF	190329	BA003DE9B3F1C5D448E15EC7B637AD8EC943B730	0000000000000000000000000000000000000000	9C4FF725A2C9F1167101139B4ED62926FD5713F77E70748568995058C86E421A
72178332	07F44C47743A2F36414A82B9F558ECFCF0EEDCEF	190329	FBB3F4BEF58373FF4C9D72AE309FEB76B3F985D2	BA003DE9B3F1C5D448E15EC7B637AD8EC943B730	9C4FF725A2C9F1167101139B4ED62926FD5713F77E70748568995058C86E421A

SELECT
    hex(token) AS token_hex,
    toString(id) AS token_id_str,
    hex(holder) AS holder_hex,
    toString(balance) AS balance_str
FROM robinhood.erc1155_balances
WHERE balance > 0
ORDER BY balance DESC
LIMIT 5

063BDBA5C8C29A57C6530F2668CFD040B1282118	1000	0000000000000000000000000000000000000000	1680
063BDBA5C8C29A57C6530F2668CFD040B1282118	6	EBB5C8D140910402FE68228E37DF8181D2F04FA1	4
063BDBA5C8C29A57C6530F2668CFD040B1282118	5	EBB5C8D140910402FE68228E37DF8181D2F04FA1	4
063BDBA5C8C29A57C6530F2668CFD040B1282118	7	009E27418A7F15623FB36C8958811BC362AFE36B	2
BAEEDDF7ADA7050A1078F27AB9F4A8C1A37AE9EC	22	0000000000000000000000000000000000000000	2

SELECT
    multiIf(
        from = unhex('0000000000000000000000000000000000000000'), 'Mint (L1->L2 Bridge In)',
        to = unhex('0000000000000000000000000000000000000000'), 'Burn (L2->L1 Bridge Out)',
        'Standard Transfer'
    ) AS transfer_category,
    count() AS transfer_count,
    toString(sum(amount)) AS total_amount_wei_str,
    divideDecimal(
        toDecimal256(sum(amount), 18),
        toDecimal256(toUInt256(concat('1', repeat('0', toUInt32(18)))), 18)
    ) AS total_amount_weth
FROM robinhood.erc20_transfers FINAL
WHERE is_deleted = 0
  AND token = unhex('0bd7d308f8e1639fab988df18a8011f41eacad73')
GROUP BY transfer_category
ORDER BY transfer_count DESC

Standard Transfer	3243	732460069856959386456	732.460069856959386456
Mint (L1->L2 Bridge In)	1220	224634953961802580164	224.634953961802580164
Burn (L2->L1 Bridge Out)	927	96594606613285049613	96.594606613285049613

SELECT
    redeem.block_number AS redeem_block,
    hex(redeem.tx_hash) AS redeem_tx_hex,
    submit.block_number AS submit_block,
    hex(submit.tx_hash) AS submit_tx_hex,
    hex(assumeNotNull(redeem.ticket_id)) AS ticket_id_hex,
    redeem.block_timestamp AS redeemed_at,
    submit.block_timestamp AS submitted_at,
    dateDiff('second', submit.block_timestamp, redeem.block_timestamp) AS bridge_delay_seconds
FROM robinhood.bridge_deposits AS redeem FINAL
JOIN robinhood.bridge_deposits AS submit FINAL
  ON redeem.ticket_id = submit.tx_hash
WHERE redeem.is_deleted = 0 AND redeem.tx_type = 104
  AND submit.is_deleted = 0 AND submit.tx_type = 105

72286551	8176F531715612D3EE4E2F4B2B8B1152DD8CB075ABB63CEC062A351E43E10440	72286551	713CF7646CC15EE5A2805456289DE6F9AF7F15B7E4C840431B8876AB5D120811	713CF7646CC15EE5A2805456289DE6F9AF7F15B7E4C840431B8876AB5D120811	2026-09-25 14:08:41	2026-09-25 14:08:41	0

SELECT
    day,
    hex(asset) AS asset_address_hex,
    deposits_count,
    toString(deposits_amount) AS deposits_amount_wei,
    withdrawals_count,
    toString(withdrawals_amount) AS withdrawals_amount_wei,
    toString(net_amount) AS net_flow_wei
FROM robinhood.bridge_daily_net_flow FINAL
ORDER BY day DESC

（0 行：样本库 bridge_erc20_gateway_events 为空；全链范围下每个「日期 × 资产」返回一行）

SELECT
    hex(creator) AS creator_address_hex,
    count() AS deployed_contracts_count,
    min(block_number) AS first_deployment_block,
    max(block_number) AS latest_deployment_block
FROM robinhood.contracts FINAL
WHERE is_deleted = 0
GROUP BY creator
ORDER BY deployed_contracts_count DESC
LIMIT 5

BD2A30302709F4FBDE18F645D1469D850EAC0B2F	2	72040328	72040871
504606DBD92873F283A6A73DCD5CC095B63A2692	1	72039992	72039992
5473FCD188D4BC727C230C193AC49AAED479FB72	1	72040374	72040374
1D17B44447EA789E6D5ABA7CF88C2C767986BB01	1	72040106	72040106
2E93F678ACEAD0E71FC0A64501D99E584ECB3C8A	1	72039023	72039023

SELECT
    hex(proxy_address) AS proxy_address_hex,
    event_type,
    multiIf(
        event_type = 'Upgraded',       hex(assumeNotNull(implementation)),
        event_type = 'AdminChanged',   concat(hex(assumeNotNull(previous_admin)), ' -> ', hex(assumeNotNull(new_admin))),
        event_type = 'BeaconUpgraded', hex(assumeNotNull(beacon)),
        'n/a'
    ) AS new_value_hex,
    block_number,
    block_timestamp,
    hex(tx_hash) AS tx_hash_hex
FROM robinhood.proxy_upgrades FINAL
WHERE is_deleted = 0
ORDER BY block_number DESC
LIMIT 5

917F9D7D42DFED3D13DFEDA3ACE523FC7F103DFC	BeaconUpgraded	A125492ACA28449D2291F5415A818697345CFA09	72039305	2026-09-25 07:12:49	DED588777A8DAAEE9A48DF55ECF97C6CC947E3BC578BCCAD24A22F72A018B4F8

SELECT
    t.block_number,
    hex(t.hash) AS tx_hash_hex,
    hex(t.from) AS from_hex,
    if(lbl_from.label = '', 'Unknown EOA/Contract', lbl_from.label) AS from_label,
    hex(assumeNotNull(t.to)) AS to_hex,
    if(lbl_to.label = '', 'Unknown Contract', lbl_to.label) AS to_label,
    if(lbl_to.category = '', 'unknown', lbl_to.category) AS to_category
FROM robinhood.transactions AS t FINAL
LEFT JOIN robinhood.address_labels AS lbl_from FINAL ON t.from = lbl_from.address
LEFT JOIN robinhood.address_labels AS lbl_to FINAL ON t.to = lbl_to.address
WHERE t.is_deleted = 0
  AND t.to IS NOT NULL
  AND lbl_to.label != ''
ORDER BY t.block_number DESC
LIMIT 5

72040999	B7193E892D042484B29D7AB074DC8AF67D0AFAFF639D9C89CCF50254BC473582	00000000000000000000000000000000000A4B05	ArbOS system sender (internal-tx from/to)	00000000000000000000000000000000000A4B05	ArbOS system sender (internal-tx from/to)	system
72040998	DA0ECF0C21D784C898A4A3D723FAC52220BAC456F6C39931D87724F5FECF51C3	00000000000000000000000000000000000A4B05	ArbOS system sender (internal-tx from/to)	00000000000000000000000000000000000A4B05	ArbOS system sender (internal-tx from/to)	system
72040997	47DBDDA528C75BBC4E3AA945E5C61AB17909DF3856FD1AA2CF8A4FD55E66BCF9	00000000000000000000000000000000000A4B05	ArbOS system sender (internal-tx from/to)	00000000000000000000000000000000000A4B05	ArbOS system sender (internal-tx from/to)	system
72040996	BBF9091C5DDA83FE7147E2A514C5AC145B8F89283899E24500B7B18D4B2038F0	00000000000000000000000000000000000A4B05	ArbOS system sender (internal-tx from/to)	00000000000000000000000000000000000A4B05	ArbOS system sender (internal-tx from/to)	system
72040995	700D0A24FDB72FF5311C223DB2ADBBD7A1EE42769426A03F9EFF1CC6D4AF5D7C	00000000000000000000000000000000000A4B05	ArbOS system sender (internal-tx from/to)	00000000000000000000000000000000000A4B05	ArbOS system sender (internal-tx from/to)	system

SELECT
    hex(token) AS token_hex,
    hex(owner) AS owner_hex,
    hex(spender) AS spender_hex,
    toString(value) AS raw_allowance_str,
    value >= bitShiftLeft(toUInt256(1), 255) AS is_unlimited_or_max,
    block_number,
    hex(tx_hash) AS tx_hash_hex
FROM robinhood.erc20_approvals FINAL
WHERE is_deleted = 0
  AND value >= bitShiftLeft(toUInt256(1), 255)
ORDER BY block_number DESC
LIMIT 5

ABADAFC3404EE07B78DD8EC4D141F07EBB2654CD	FB5B10615A2008C6507D9FA02A55310159969F1E	7EAF2DB390B7A68FFD6586BDE156138D683E748C	115792089237316195423570985008687907853269984665640564039457584007913129639935	1	72040999	016536A80B8280C4AC66206A445FCF29264342DAAA3C6727FD333AE8629A5575
ABADAFC3404EE07B78DD8EC4D141F07EBB2654CD	FEFE306EB0E5EAF0D6821A1D2935FFC5CFA8CC1B	7EAF2DB390B7A68FFD6586BDE156138D683E748C	115792089237316195423570985008687907853269984665640564039457584007913129639935	1	72040997	9270A0B352CFF581269F887E24FE132A7AC9935A6C1E3730F99B93EC168D1FAA
ABADAFC3404EE07B78DD8EC4D141F07EBB2654CD	E4A727D2CCA580D95E52D55930F6AAC12EC61075	7EAF2DB390B7A68FFD6586BDE156138D683E748C	115792089237316195423570985008687907853269984665640564039457584007913129639935	1	72040997	93FE70F32F709BF865797C829191EB4469CE71A75F614E1B5B156E47F1CE0CA8
73C2E594371F11AD5C057EDAD727D5273B3499CA	7642F1837F5347B157883322713FFCBCC60AA311	000000000022D473030F116DDEE9F6B43AC78BA3	115792089237316195423570985008687907853269984665640564039457584007913129639935	1	72040994	B078FE9F861714EBA2DF6486ACACC3AB8B97E93CE624F248118E5BB3738E2474
34C00991F46B21D5CF5EAC72F612A390A1C5E01F	48AD7AEA7A5365CC89EE4E075246B4D6349BFB2F	CA651C9232FB2C9F3DCBCAD1043B29B2B5833106	115792089237316195423570985008687907853269984665640564039457584007913129639935	1	72040988	33A8BDA5529440FD2B7E5A7FE37B4A26200AD8FA6C10747B1FEA3A5D8CCFB1B2

SELECT
    hex(l.topic0) AS topic0_hex,
    if(e.name = '', 'Unknown Event', e.name) AS event_name,
    if(e.signature = '', 'N/A', e.signature) AS event_signature,
    if(e.source = '', 'custom', e.source) AS event_source,
    count() AS event_occurrences
FROM robinhood.logs AS l FINAL
LEFT JOIN robinhood.event_signatures AS e ON l.topic0 = e.topic0
WHERE l.is_deleted = 0 AND l.topic0 IS NOT NULL
GROUP BY l.topic0, e.name, e.signature, e.source
ORDER BY event_occurrences DESC
LIMIT 5

DDF252AD1BE2C89B69C2B068FC378DAA952BA7F163C4A11628F55A4DF523B3EF	Transfer	Transfer(address,address,uint256)	ERC20/ERC721	24035
8C5BE1E5EBEC7D5BD14F71427D1E84F3DD0314C0F7B2291E5B200AC8C7C3B925	Approval	Approval(address,address,uint256)	ERC20/ERC721	3431
40E9CECB9F5F1F1C5B9C97DEC2917B7EE92E57BA5563708DACA94DD84AD7112F	Swap	Swap(bytes32,address,int128,int128,uint160,uint128,int24,uint24)	UniswapV4	2773
C42079F94A6350D7E6235F29174924F928CC2AC818EB64FED8004E115FBCCA67	Swap	Swap(address,address,int256,int256,uint160,uint128,int24)	UniswapV3	2451
37E7F0DB430EDC9DD31BC66F25F8449353AA0818F503B906747DD8F286CD3802	Unknown Event	N/A	custom	1484

SELECT
    block_number,
    hex(tx_hash) AS tx_hash_hex,
    depth,
    call_type,
    hex(from) AS from_hex,
    hex(assumeNotNull(to)) AS to_hex,
    toString(value) AS value_wei_str,
    gas_used,
    error
FROM robinhood.traces FINAL
WHERE is_deleted = 0
  AND block_number >= 72051000 AND block_number < 72051010
ORDER BY block_number, tx_index, depth
LIMIT 5
SETTINGS max_threads=2, max_execution_time=60

72051000	0446A34EF60A30F8F6BEC8718E8497AF7717BFB1427119BEB9AD5C37B36AB65D	0	CALL	00000000000000000000000000000000000A4B05	00000000000000000000000000000000000A4B05	0	0	
72051000	0446A34EF60A30F8F6BEC8718E8497AF7717BFB1427119BEB9AD5C37B36AB65D	1	CALL	FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFE	0000F90827F1C53A10CB7A02335B175320002935	0	8492	
72051000	0446A34EF60A30F8F6BEC8718E8497AF7717BFB1427119BEB9AD5C37B36AB65D	2	STATICCALL	0000F90827F1C53A10CB7A02335B175320002935	0000000000000000000000000000000000000064	0	803	
72051000	FE2BFA6F280F7FCBFD94F1D15DA0069FE0C9834879149FCA16ED278D93CC3983	0	CALL	20AA7E1E24B87E2EBB5C8487157044AC07E33B91	E03952268A04AFE16CC1B02C612478BD54D6B7A6	0	130407	
72051000	FE2BFA6F280F7FCBFD94F1D15DA0069FE0C9834879149FCA16ED278D93CC3983	1	STATICCALL	E03952268A04AFE16CC1B02C612478BD54D6B7A6	0BD7D308F8E1639FAB988DF18A8011F41EACAD73	0	9787

SELECT
    c.block_number,
    hex(c.address) AS deployed_contract_hex,
    hex(c.creator) AS creator_hex,
    hex(c.creation_tx_hash) AS creation_tx_hex,
    c.via,
    length(t.input) AS init_code_length_bytes
FROM robinhood.contracts AS c FINAL
LEFT JOIN robinhood.transactions AS t FINAL ON c.creation_tx_hash = t.hash
WHERE c.is_deleted = 0
ORDER BY c.block_number DESC
LIMIT 5

72040871	B5F0612EFC5D5EF0E16E3BA1ED6AFB6B7E67429B	BD2A30302709F4FBDE18F645D1469D850EAC0B2F	0DA3BD480A619E41B30B9186B7E597F6EEF5A387B2E4BB29FE67E8B04C6C1D21	tx	16809
72040374	611CB250B831C791765A864AE690E83C031D3FB7	5473FCD188D4BC727C230C193AC49AAED479FB72	CE358A89D21937AEFDFEA85D27F34442DE186D7190D95F39B480FD159114C9BC	tx	23421
72040328	F59580BE27AB1A8041A8053351F5D42622D2A462	BD2A30302709F4FBDE18F645D1469D850EAC0B2F	B2491E3016667822AA5EC61254927C9C8E39456C090F19A2F8893AB50693FD22	tx	8609
72040221	FCB2963337AE428FE40B4A0D96C8DD6115CE1AD7	251E39FB07D89C3A7C5F75ED82304F5EEB15E4F9	DCC046F6BA14BFA032CFB271895057049B85E7B803241B8611C8720615760576	tx	3717
72040106	64446BDDEEFCB2A7B73A491BB0EFD81DC2271D9F	1D17B44447EA789E6D5ABA7CF88C2C767986BB01	40E66C1F11C166EECD2EF49FF168675251B319400A3E16FC029CD3C9A72C18BA	tx	7439

-- {db} 替换为实际库名（生产是 robinhood；035 尚未安装到 robinhood，示例输出来自 scratch 库 holder_bench）
-- ① 最新 5 条（分页时把 block_number 游标下界放进 WHERE，并 ORDER BY block_number DESC, log_index DESC）
SELECT
    block_number,
    log_index,
    if(direction = 0, 'out', 'in') AS direction,
    hex(counterparty) AS counterparty_hex,
    hex(token) AS token_hex,
    toString(amount) AS raw_amount_str,
    hex(tx_hash) AS tx_hash_hex,
    block_timestamp
FROM {db}.erc20_transfers_by_holder FINAL
WHERE is_deleted = 0
  AND holder = unhex('caf681a66d020601342297493863e78c959e5cb2')
ORDER BY block_number DESC, log_index DESC
LIMIT 5
SETTINGS max_threads=4;

-- ② 全量计数（+ 首末区块，用同一个键范围）
SELECT count() AS transfers, min(block_number) AS first_block, max(block_number) AS last_block
FROM {db}.erc20_transfers_by_holder FINAL
WHERE is_deleted = 0
  AND holder = unhex('caf681a66d020601342297493863e78c959e5cb2')
SETTINGS max_threads=4;

72853165	2	out	8A207F8A985B7996D9CD4AF6469EB1768BC920C3	0BD7D308F8E1639FAB988DF18A8011F41EACAD73	29700000000000000	63B2EE477994FDA95151DE8E959947B762DBC127D1ED0C13E01CF606A696918A	2026-09-26 06:01:06
72853165	1	in	0000000000000000000000000000000000000000	0BD7D308F8E1639FAB988DF18A8011F41EACAD73	29700000000000000	63B2EE477994FDA95151DE8E959947B762DBC127D1ED0C13E01CF606A696918A	2026-09-26 06:01:06
72853163	15	out	94BBE853481F40D049D98EB830C57276D9727E5B	0BD7D308F8E1639FAB988DF18A8011F41EACAD73	199000000000000000	EE6B569BA49B189EDE2AC68049DA5814ECA71B369CDFF65BCC2B40945CA15A3D	2026-09-26 06:01:06
72853163	14	in	0000000000000000000000000000000000000000	0BD7D308F8E1639FAB988DF18A8011F41EACAD73	199000000000000000	EE6B569BA49B189EDE2AC68049DA5814ECA71B369CDFF65BCC2B40945CA15A3D	2026-09-26 06:01:06
72853157	10	out	5CF061D6CCFF58E95D219D26295B73207ED38B66	0BD7D308F8E1639FAB988DF18A8011F41EACAD73	225525079999428180	9A5FBCFC9BCA7E84077FFD5B877DBBDF2D6E4BDCDAD60E7B5D6582518F8A4EFC	2026-09-26 06:01:05

115405086	58821	72853165

-- {db} 同上（生产 robinhood 尚未安装；示例输出来自 scratch 库 holder_bench）
SELECT
    block_number,
    log_index,
    if(direction = 0, 'out', 'in') AS direction,
    hex(counterparty) AS counterparty_hex,
    hex(token) AS token_hex,
    toString(token_id) AS token_id_str,
    hex(tx_hash) AS tx_hash_hex
FROM {db}.erc721_transfers_by_holder FINAL
WHERE is_deleted = 0
  AND holder = unhex('2b5b35ac5a2d5c1224337ba86bf3816abee69da3')
ORDER BY block_number DESC, log_index DESC
LIMIT 5
SETTINGS max_threads=4;

SELECT count() AS transfers FROM {db}.erc721_transfers_by_holder FINAL
WHERE is_deleted = 0
  AND holder = unhex('2b5b35ac5a2d5c1224337ba86bf3816abee69da3')
SETTINGS max_threads=4;

72842703	35	in	0000000000000000000000000000000000000000	F3DB4C7E01135B65FF1171481AFE3F6D099A8548	738	483C4E0614945E5C64639DC07C0604777A2F080C63F7CAE88B208BECE72F6F79
72842703	34	in	0000000000000000000000000000000000000000	F3DB4C7E01135B65FF1171481AFE3F6D099A8548	737	483C4E0614945E5C64639DC07C0604777A2F080C63F7CAE88B208BECE72F6F79
72842703	33	in	0000000000000000000000000000000000000000	F3DB4C7E01135B65FF1171481AFE3F6D099A8548	736	483C4E0614945E5C64639DC07C0604777A2F080C63F7CAE88B208BECE72F6F79
72842703	32	in	0000000000000000000000000000000000000000	F3DB4C7E01135B65FF1171481AFE3F6D099A8548	735	483C4E0614945E5C64639DC07C0604777A2F080C63F7CAE88B208BECE72F6F79
72842703	31	in	0000000000000000000000000000000000000000	F3DB4C7E01135B65FF1171481AFE3F6D099A8548	734	483C4E0614945E5C64639DC07C0604777A2F080C63F7CAE88B208BECE72F6F79

488942

-- ① 先求时间窗对应的块号范围（示例：2026-09-26 02:00–03:00 UTC）
SELECT min(number) AS min_block, max(number) AS max_block
FROM robinhood.blocks FINAL
WHERE is_deleted = 0
  AND timestamp >= toDateTime('2026-09-26 02:00:00', 'UTC')
  AND timestamp <  toDateTime('2026-09-26 03:00:00', 'UTC');
-- → 72710510 / 72746055（闭区间、含两端；2026-09-25 UTC 全天 = 71782334..72639310）

-- ② 按时间查区块：不要按 timestamp 过滤（实测 2.31s / 7,022 万行），改用块号范围（8ms / 6.8 万行）
SELECT number, timestamp, tx_count FROM robinhood.blocks FINAL
WHERE is_deleted = 0 AND number >= 72710510 AND number <= 72746055   -- = ① 的 min_block .. max_block
ORDER BY number DESC
LIMIT 100;

-- ③ 某地址的交易（docs/queries.md 例1 改写版）：加"最近 24h"块号下界
--    实测 17,757ms（2.21 亿行）→ 74ms（53 万行）；"全部交易 count()" 8,993ms → 173ms
--    ⚠️ 下界用 ① 现场算出的 min_block，不要写死：71782334 是 2026-09-25 UTC 00:00 起点，
--       窗口 B（09-26 03:55 UTC）实测时它实际覆盖 ≈27.9 h；日期一变它就不再是"最近 24h"（会越查越多），这里只是示例值
SELECT block_number, hex(hash) AS tx_hash_hex, hex(`from`) AS from_hex, hex(`to`) AS to_hex,
       toString(value) AS value_str, status
FROM robinhood.transactions FINAL
WHERE (`from` = unhex('<addr>') OR `to` = unhex('<addr>'))
  AND is_deleted = 0
  AND block_number >= 71782334          -- ← 只加这一行：最近 N 天/小时的块号下界
ORDER BY block_number DESC
LIMIT 50;

-- ④ 某代币单日转账：token 前缀 + 块号范围（排序键 (token, block_number, log_index) 对齐）
SELECT count() AS n
FROM robinhood.erc20_transfers FINAL
WHERE is_deleted = 0
  AND token = unhex('0bd7d308f8e1639fab988df18a8011f41eacad73')
  AND block_number >= 71782334 AND block_number < 72639311   -- 2026-09-25 UTC

-- ⑤ 某合约在时间窗内的日志：同样先转成块号范围（1 小时窗实测 7ms）
SELECT count() AS n
FROM robinhood.logs FINAL
WHERE is_deleted = 0
  AND address = unhex('0bd7d308f8e1639fab988df18a8011f41eacad73')
  AND block_number >= 72710510 AND block_number < 72746056

-- ⑥ 按哈希查交易 / 按 tx_hash 查日志
--    现状（`sql/indexes.sql` 已落地）：bloom 只有 ~15x 行剪枝（granule 口径 42–43x），仍未到点查量级——
--    实测：按 hash 查交易 16.5s / 5,596 万行；按 tx_hash 查日志 5.8s / 2.40 亿行（max_threads=4）
--    ✅ 实用做法：先拿到（已知的）块高，再叠加 block_number 下界收窄。实测（窗口 B，索引生效，
--       样本 hash `3734cfe2…` 真实块高 71,903,499，下界取 71,000,000）：
--         transactions count() 0.040s / 126 万行；SELECT * 0.45s / 126 万行 / 1.50 GB
--         logs         count() 0.073s / 478 万行；取 5 行日志 0.11s / 268 MB
--       下界越贴近真实块高越省。要稳定亚秒级仍需窄派生表（见基线文档 §7.2）
--    ⚠️ 下界必须 ≤ 交易真实块高：下界猜大了会返回 0 行，那是"范围不对"而不是"交易不存在"；
--       （实测反例：同一样本用下界 72,000,000 → 0.024s 返回 0 行，而它其实存在）
--       不确定块高时先按 ① 缩小时间窗，或接受一次全表扫描
SELECT * FROM robinhood.transactions FINAL
WHERE hash = unhex('<tx_hash>') AND is_deleted = 0
  AND block_number >= 71000000;                -- ← 用已知（非估计）且 ≤ 真实块高的下界收窄；示例 hash 真实块高 71,903,499

-- ⑦ 某地址（holder）的 ERC20 转账：表已被全量重建为 15.35 亿行，**全历史** from/to 过滤会跑满 120s
--    （E159 超时，读 10 亿行 / 82 GB 仍未扫完）；加"最近 N 天"块号下界后实测 3.1s（read 1,469 万行 / 1.29 GB）
--    ⚠️ 下界必须用 ① 现场算出的 min_block，不要写死；示例值 72,639,311 是 **2026-09-26 00:00 UTC** 的起点，
--       只覆盖到 2026-09-26 05:53 UTC 那次测量的 ~5.9 小时（该窗口只有该 holder 24h 转账量的 ~35%）。
--       真 24h（下界 71,983,710 = 2026-09-25 05:39 UTC）实测 1.05s / 2,351 万行 / 1.75 GiB（返回 424,034 笔）。
SELECT block_number, hex(tx_hash) AS tx_hash_hex, hex(token) AS token_hex,
       toString(amount) AS amount_str, hex(`from`) AS from_hex, hex(`to`) AS to_hex
FROM robinhood.erc20_transfers FINAL
WHERE is_deleted = 0
  AND (`from` = unhex('<addr>') OR `to` = unhex('<addr>'))
  AND block_number >= 72639311                 -- ← 只加这一行：最近 N 天/小时的块号下界（示例 = 09-26 00:00 UTC，不是"昨天全天"）
ORDER BY block_number DESC
LIMIT 50;