Query editor
-- BTC mining pool distribution / hashshare validation, last 7 days---- METHODOLOGY: Pool identification from the coinbase tx input bytes.-- This is a multi-strategy decoder, in priority order:-- 1. Substring match on known pool signatures (handles all the major pools-- regardless of whether they wrap their tag in slashes).-- 2. Fallback regex extraction of /tag/ patterns for less common pools.-- 3. Otherwise classified as "Unknown".---- HISTORY: An earlier version of this query (regex-only) missed Foundry USA,-- AntPool, and MARA — the three largest US pools — because:-- - Foundry uses /Foundry USA Pool #dropgold/ (the # broke a char-class regex)-- - AntPool uses "Mined by AntPool901j" (no slashes at all)-- - MARA uses "i| MARA Made in USA" (pipe + no slash)-- See investigation query 7415109 for the empirical autopsy.WITH coinbase_tx AS (SELECTdate,height,from_utf8(coinbase) AS coinbase_textFROM bitcoin.blocksWHERE date >= current_date - interval '7' day),tagged AS (SELECTdate,height,CASE-- Known pool signatures (substring match, priority order)WHEN position('Foundry USA' IN coinbase_text) > 0 THEN 'Foundry USA'WHEN position('AntPool' IN coinbase_text) > 0 THEN 'AntPool'WHEN position('MARA' IN coinbase_text) > 0 THEN 'MARA Pool'WHEN position('ViaBTC' IN coinbase_text) > 0 THEN 'ViaBTC'WHEN position('F2Pool' IN coinbase_text) > 0OR position('七彩神仙鱼' IN coinbase_text) > 0 THEN 'F2Pool'WHEN position('SBICrypto' IN coinbase_text) > 0 THEN 'SBI Crypto'WHEN position('Braiins' IN coinbase_text) > 0OR position('SlushPool' IN coinbase_text) > 0OR position('slush' IN coinbase_text) > 0 THEN 'Braiins Pool (slush)'WHEN position('Binance Pool' IN coinbase_text) > 0OR position('binance' IN coinbase_text) > 0 THEN 'Binance Pool'WHEN position('Luxor' IN coinbase_text) > 0 THEN 'Luxor'WHEN position('OCEAN' IN coinbase_text) > 0OR position('ocean.xyz' IN coinbase_text) > 0 THEN 'OCEAN'WHEN position('SECPOOL' IN coinbase_text) > 0OR position('SecPool' IN coinbase_text) > 0 THEN 'SECPOOL'WHEN position('ULTIMUSPOOL' IN coinbase_text) > 0OR position('ultimus' IN coinbase_text) > 0 THEN 'ULTIMUS'WHEN position('Poolin' IN coinbase_text) > 0OR position('poolin' IN coinbase_text) > 0 THEN 'Poolin'WHEN position('SpiderPool' IN coinbase_text) > 0OR position('spiderpool' IN coinbase_text) > 0 THEN 'SpiderPool'WHEN position('CarbonNegative' IN coinbase_text) > 0OR position('TerraPool' IN coinbase_text) > 0 THEN 'TerraPool'WHEN position('NiceHash' IN coinbase_text) > 0 THEN 'NiceHash'WHEN position('SoloCK' IN coinbase_text) > 0 THEN 'SoloCKpool'-- Fallback: any /tag/ pattern with broader char class (printable ASCII)WHEN regexp_like(coinbase_text, '/[\x21-\x7E]{3,40}?/')