CATCloud

-- CATCLOUD WITH early_blocks AS ( SELECT block_number, block_timestamp, transaction_hash FROM `bigquery-public-data.crypto_bitcoin.transactions` WHERE block_number BETWEEN 0 AND 50000 ), early_outputs AS ( SELECT t.hash AS transaction_hash, t.block_number, t.block_timestamp, output.addresses[SAFE_OFFSET(0)] AS wallet_address, output.value / 100000000 AS btc_value FROM `bigquery-public-data.crypto_bitcoin.transactions` t, UNNEST(t.outputs) AS output WHERE t.block_number BETWEEN 0 AND 50000 AND output.value > 0 ), wallet_clusters AS ( SELECT wallet_address, COUNT(*) AS times_seen, SUM(btc_value) AS total_btc_received, MIN(block_number) AS first_seen_block, MAX(block_number) AS last_seen_block FROM early_outputs WHERE wallet_address IS NOT NULL GROUP BY wallet_address ) SELECT wallet_address, total_btc_received, times_seen, first_seen_block, last_seen_block, CASE WHEN first_seen_block < 1000 THEN 'GENESIS ERA 👀' WHEN first_seen_block < 10000 THEN 'VERY EARLY MINER' ELSE 'EARLY BITCOIN WALLET' END AS wallet_signal FROM wallet_clusters WHERE total_btc_received >= 50 ORDER BY first_seen_block ASC, total_btc_received DESC LIMIT 21;