-- with avalanche_balances as ( -- SELECT DATE_TRUNC('day', block_timestamp) AS date, -- to_address, -- SUM(CASE WHEN from_address = to_address THEN raw_amount/power(10, decimals) ELSE 0 END) OVER (PARTITION BY to_address ORDER BY DATE_TRUNC('day', block_timestamp)) AS sent_balance, -- SUM(CASE WHEN from_address <> to_address THEN raw_amount/power(10, decimals) ELSE 0 END) OVER (PARTITION BY to_address ORDER BY DATE_TRUNC('day', block_timestamp)) AS received_balance -- FROM avalanche.core.ez_token_transfers -- WHERE lower(contract_address) = lower('0xB97EF9Ef8734C71904D8002F8b6Bc66Dd9c48a6E') -- OR lower(contract_address) = lower('0xA7D7079b0FEaD91F3e65f86E8915Cb59c1a4C664') -- bridged usdc -- ), with avalanche_data AS ( SELECT COALESCE(tab1.from_address, tab2.to_address) AS address, COALESCE(tab2.USDC_In, 0) - COALESCE(tab1.USDC_out, 0) AS balance FROM ( SELECT from_address, SUM(raw_amount / POWER(10, decimals)) AS USDC_out FROM avalanche.core.ez_token_transfers WHERE contract_address LIKE LOWER('0xB97EF9Ef8734C71904D8002F8b6Bc66Dd9c48a6E') GROUP BY from_address ) AS tab1 FULL JOIN ( SELECT to_address, SUM(raw_amount / POWER(10, decimals)) AS USDC_In FROM avalanche.core.ez_token_transfers WHERE contract_address LIKE LOWER('0xB97EF9Ef8734C71904D8002F8b6Bc66Dd9c48a6E') GROUP BY to_address ) AS tab2 ON tab1.from_address = tab2.to_address ), polygon_data AS ( SELECT COALESCE(tab1.from_address, tab2.to_address) AS address, COALESCE(tab2.USDC_In, 0) - COALESCE(tab1.USDC_out, 0) AS balance FROM ( SELECT from_address, SUM(raw_amount / POWER(10, decimals)) AS USDC_out FROM polygon.core.ez_token_transfers WHERE contract_address LIKE LOWER('0x2791Bca1f2de4661ED88A30C99A7a9449Aa84174') GROUP BY from_address ) AS tab1 FULL JOIN ( SELECT to_address, SUM(raw_amount / POWER(10, decimals)) AS USDC_In FROM polygon.core.ez_token_transfers WHERE contract_address LIKE LOWER('0x2791Bca1f2de4661ED88A30C99A7a9449Aa84174') GROUP BY to_address ) AS tab2 ON tab1.from_address = tab2.to_address ), -- polygon_balances as ( -- SELECT DATE_TRUNC('day', block_timestamp) AS date, -- to_address, -- SUM(CASE WHEN from_address = to_address THEN raw_amount/power(10, decimals) ELSE 0 END) OVER (PARTITION BY to_address ORDER BY DATE_TRUNC('day', block_timestamp)) AS sent_balance, -- SUM(CASE WHEN from_address <> to_address THEN raw_amount/power(10, decimals) ELSE 0 END) OVER (PARTITION BY to_address ORDER BY DATE_TRUNC('day', block_timestamp)) AS received_balance -- FROM polygon.core.ez_token_transfers -- WHERE lower(contract_address) = lower('0x2791Bca1f2de4661ED88A30C99A7a9449Aa84174') -- ), optimism_data AS ( SELECT COALESCE(tab1.from_address, tab2.to_address) AS address, COALESCE(tab2.USDC_In, 0) - COALESCE(tab1.USDC_out, 0) AS balance FROM ( SELECT from_address, SUM(raw_amount / POWER(10, decimals)) AS USDC_out FROM optimism.core.ez_token_transfers WHERE contract_address LIKE LOWER('0x7F5c764cBc14f9669B88837ca1490cCa17c31607') GROUP BY from_address ) AS tab1 FULL JOIN ( SELECT to_address, SUM(raw_amount / POWER(10, decimals)) AS USDC_In FROM optimism.core.ez_token_transfers WHERE contract_address LIKE LOWER('0x7F5c764cBc14f9669B88837ca1490cCa17c31607') GROUP BY to_address ) AS tab2 ON tab1.from_address = tab2.to_address ), -- optimism_balances as ( -- SELECT DATE_TRUNC('day', block_timestamp) AS date, -- to_address, -- SUM(CASE WHEN from_address = to_address THEN raw_amount/power(10, decimals) ELSE 0 END) OVER (PARTITION BY to_address ORDER BY DATE_TRUNC('day', block_timestamp)) AS sent_balance, -- SUM(CASE WHEN from_address <> to_address THEN raw_amount/power(10, decimals) ELSE 0 END) OVER (PARTITION BY to_address ORDER BY DATE_TRUNC('day', block_timestamp)) AS received_balance -- FROM optimism.core.ez_token_transfers -- WHERE lower(contract_address) = lower('0x7F5c764cBc14f9669B88837ca1490cCa17c31607') -- ), arbitrum_data AS ( SELECT COALESCE(tab1.from_address, tab2.to_address) AS address, COALESCE(tab2.USDC_In, 0) - COALESCE(tab1.USDC_out, 0) AS balance FROM ( SELECT from_address, SUM(raw_amount / POWER(10, decimals)) AS USDC_out FROM arbitrum.core.ez_token_transfers WHERE lower(contract_address) = lower('0xaf88d065e77c8cC2239327C5EDb3A432268e5831') OR lower(contract_address) = lower('0xFF970A61A04b1cA14834A43f5dE4533eBDDB5CC8') GROUP BY from_address ) AS tab1 FULL JOIN ( SELECT to_address, SUM(raw_amount / POWER(10, decimals)) AS USDC_In FROM arbitrum.core.ez_token_transfers WHERE lower(contract_address) = lower('0xaf88d065e77c8cC2239327C5EDb3A432268e5831') OR lower(contract_address) = lower('0xFF970A61A04b1cA14834A43f5dE4533eBDDB5CC8') GROUP BY to_address ) AS tab2 ON tab1.from_address = tab2.to_address ), -- arbitrum_balances as ( -- SELECT DATE_TRUNC('day', block_timestamp) AS date, -- to_address, -- SUM(CASE WHEN from_address = to_address THEN raw_amount/power(10, decimals) ELSE 0 END) OVER (PARTITION BY to_address ORDER BY DATE_TRUNC('day', block_timestamp)) AS sent_balance, -- SUM(CASE WHEN from_address <> to_address THEN raw_amount/power(10, decimals) ELSE 0 END) OVER (PARTITION BY to_address ORDER BY DATE_TRUNC('day', block_timestamp)) AS received_balance -- FROM arbitrum.core.ez_token_transfers -- WHERE lower(contract_address) = lower('0xaf88d065e77c8cC2239327C5EDb3A432268e5831') -- OR lower(contract_address) = lower('0xFF970A61A04b1cA14834A43f5dE4533eBDDB5CC8') -- ), -- categories AS ( -- SELECT -- CASE -- WHEN (received_balance - sent_balance) < 10 THEN 'Shrimp (< 10 USDC)' -- WHEN (received_balance - sent_balance) >= 10 AND (received_balance - sent_balance) < 100 THEN 'Crab (< 100 USDC)' -- WHEN (received_balance - sent_balance) >= 100 AND (received_balance - sent_balance) < 1000 THEN 'Octopus (< 1,000 USDC)' -- WHEN (received_balance - sent_balance) >= 1000 AND (received_balance - sent_balance) < 10000 THEN 'Fish (< 10,000 USDC)' -- WHEN (received_balance - sent_balance) >= 10000 AND (received_balance - sent_balance) < 100000 THEN 'Shark (< 100,000 USDC)' -- WHEN (received_balance - sent_balance) >= 100000 THEN 'Whale (> 100,000 USDC)' -- END AS category, -- COUNT(to_address) AS holders -- FROM balances -- GROUP BY category -- ), avax_holders as ( SELECT 'Avalanche' as chain, CASE WHEN balance > 0.1 AND balance < 10 THEN 'Shrimp (< 10 USDC)' WHEN balance >= 10 AND balance < 100 THEN 'Crab (< 100 USDC)' WHEN balance >= 100 AND balance < 1000 THEN 'Octopus (< 1,000 USDC)' WHEN balance >= 1000 AND balance < 10000 THEN 'Fish (< 10,000 USDC)' WHEN balance >= 10000 AND balance < 100000 THEN 'Shark (< 100,000 USDC)' WHEN balance >= 100000 THEN 'Whale (> 100,000 USDC)' END AS category, COUNT(address) AS holders FROM avalanche_data WHERE category IS NOT NULL GROUP BY category ), polygon_holders as ( SELECT 'Polygon' as chain, CASE WHEN balance > 0.1 AND balance < 10 THEN 'Shrimp (< 10 USDC)' WHEN balance >= 10 AND balance < 100 THEN 'Crab (< 100 USDC)' WHEN balance >= 100 AND balance < 1000 THEN 'Octopus (< 1,000 USDC)' WHEN balance >= 1000 AND balance < 10000 THEN 'Fish (< 10,000 USDC)' WHEN balance >= 10000 AND balance < 100000 THEN 'Shark (< 100,000 USDC)' WHEN balance >= 100000 THEN 'Whale (> 100,000 USDC)' END AS category, COUNT(address) AS holders FROM polygon_data WHERE category IS NOT NULL GROUP BY category ), optimism_holders as ( SELECT 'Optimism' as chain, CASE WHEN balance > 0.1 AND balance < 10 THEN 'Shrimp (< 10 USDC)' WHEN balance >= 10 AND balance < 100 THEN 'Crab (< 100 USDC)' WHEN balance >= 100 AND balance < 1000 THEN 'Octopus (< 1,000 USDC)' WHEN balance >= 1000 AND balance < 10000 THEN 'Fish (< 10,000 USDC)' WHEN balance >= 10000 AND balance < 100000 THEN 'Shark (< 100,000 USDC)' WHEN balance >= 100000 THEN 'Whale (> 100,000 USDC)' END AS category, COUNT(address) AS holders FROM optimism_data WHERE category IS NOT NULL GROUP BY category ), arbitrum_holders as ( SELECT 'Arbitrum' as chain, CASE WHEN balance > 0.1 AND balance < 10 THEN 'Shrimp (< 10 USDC)' WHEN balance >= 10 AND balance < 100 THEN 'Crab (< 100 USDC)' WHEN balance >= 100 AND balance < 1000 THEN 'Octopus (< 1,000 USDC)' WHEN balance >= 1000 AND balance < 10000 THEN 'Fish (< 10,000 USDC)' WHEN balance >= 10000 AND balance < 100000 THEN 'Shark (< 100,000 USDC)' WHEN balance >= 100000 THEN 'Whale (> 100,000 USDC)' END AS category, COUNT(address) AS holders FROM arbitrum_data WHERE category IS NOT NULL GROUP BY category ) SELECT * FROM avax_holders UNION ALL SELECT * FROM polygon_holders UNION ALL SELECT * FROM optimism_holders UNION ALL SELECT * FROM arbitrum_holders

USDC Olympics: Layer 2s vs Avalanche