with ethereum_usdc_transfers as ( select block_timestamp, tx_hash, block_number, from_address as sender, to_address as receiver, (raw_amount / pow(10, 6)) as transfer_amount, 'ETHEREUM' as chain from ethereum.core.ez_token_transfers where contract_address = '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48' and symbol = 'USDC' and raw_amount::float > 0 and from_address != '0x0000000000000000000000000000000000000000' and to_address != '0x0000000000000000000000000000000000000000' and BLOCK_TIMESTAMP::DATE < CURRENT_DATE AND BLOCK_TIMESTAMP::DATE >= CURRENT_DATE-{{NO_OF_PRIOR_DAYS}} ), algorand_usdc_transfers as ( select block_timestamp, tx_id as tx_hash, block_id as block_number, tx_sender as sender, receiver, amount::float as transfer_amount, 'ALGORAND' as chain from algorand.core.ez_transfer where asset_name = 'USDC' and asset_id = 31566704 and amount::float > 0 and tx_type_name = 'asset transfer' and BLOCK_TIMESTAMP::DATE < CURRENT_DATE AND BLOCK_TIMESTAMP::DATE >= CURRENT_DATE-{{NO_OF_PRIOR_DAYS}} ), solana_usdc_transfers as ( select block_timestamp, tx_id as tx_hash, block_id as block_number, tx_from as sender, tx_to as receiver, amount::float as transfer_amount, 'SOLANA' as chain from solana.core.fact_transfers where mint = 'EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v' and amount::float > 0 and BLOCK_TIMESTAMP::DATE < CURRENT_DATE AND BLOCK_TIMESTAMP::DATE >= CURRENT_DATE-{{NO_OF_PRIOR_DAYS}} ), optimism_usdc_transfers as ( select block_timestamp, tx_hash, block_number, from_address as sender, to_address as receiver, (raw_amount / pow(10, 6)) as transfer_amount, 'OPTIMISM' as chain from optimism.core.fact_token_transfers where contract_address = '0x7f5c764cbc14f9669b88837ca1490cca17c31607' and raw_amount::float > 0 and BLOCK_TIMESTAMP::DATE < CURRENT_DATE AND BLOCK_TIMESTAMP::DATE >= CURRENT_DATE-{{NO_OF_PRIOR_DAYS}} ), osmosis_usdc_transfers as ( select block_timestamp, tx_id as tx_hash, block_id as block_number, sender, receiver, (amount / pow(10, 6))::float as transfer_amount, 'OSMOSIS' as chain from osmosis.core.fact_transfers where currency in ('ibc/D189335C6E4A68B513C10AB227BF1C1D38C746766278BA3EEB4FB14124F1D858', 'uusdc') and amount::float > 0 and tx_status = 'SUCCEEDED' and BLOCK_TIMESTAMP::DATE < CURRENT_DATE AND BLOCK_TIMESTAMP::DATE >= CURRENT_DATE-{{NO_OF_PRIOR_DAYS}} ), axelar_usdc_transfers as ( select block_timestamp, tx_id as tx_hash, block_id as block_number, sender, receiver, (amount / pow(10, 6))::float as transfer_amount, 'AXELAR' as chain from axelar.core.fact_transfers where currency = 'uusdc' and amount::float > 0 and tx_succeeded = true and BLOCK_TIMESTAMP::DATE < CURRENT_DATE AND BLOCK_TIMESTAMP::DATE >= CURRENT_DATE-{{NO_OF_PRIOR_DAYS}} ), near_usdc_transfers as ( select block_timestamp, tx_hash, block_id as block_number, split(logs[0], ' ')[3]::string as sender, split(logs[0], ' ')[5]::string as receiver, (split(logs[0], ' ')[1] / pow(10, 6))::float as transfer_amount, 'NEAR' as chain from near.core.fact_receipts where receiver_id = 'a0b86991c6218b36c1d19d4a2e9eb0ce3606eb48.factory.bridge.near' and logs[0] is not null and split(logs[0], ' ')[1] > 0 and split(logs[0], ' ')[1] is not null and logs[0] like 'Transfer%from%to%' and BLOCK_TIMESTAMP::DATE < CURRENT_DATE AND BLOCK_TIMESTAMP::DATE >= CURRENT_DATE-{{NO_OF_PRIOR_DAYS}} ), flow_usdc_withdrawn as ( select block_timestamp, tx_id as tx_hash, block_height as block_number, event_data:from as sender, (event_data:amount)::float as transfer_amount from flow.core.fact_events where event_contract = 'A.b19436aae4d94622.FiatToken' and event_type = 'TokensWithdrawn' and event_data:from is not null and event_data:from like '0x%' and event_data:amount::float > 0 and tx_succeeded = true and BLOCK_TIMESTAMP::DATE < CURRENT_DATE AND BLOCK_TIMESTAMP::DATE >= CURRENT_DATE-{{NO_OF_PRIOR_DAYS}} ), flow_usdc_deposited as ( select tx_id as tx_hash, event_data:to as receiver, (event_data:amount)::float as transfer_amount from flow.core.fact_events where event_contract = 'A.b19436aae4d94622.FiatToken' and event_type = 'TokensDeposited' and event_data:to is not null and event_data:to like '0x%' and event_data:amount::float > 0 and tx_succeeded = true and BLOCK_TIMESTAMP::DATE < CURRENT_DATE AND BLOCK_TIMESTAMP::DATE >= CURRENT_DATE-{{NO_OF_PRIOR_DAYS}} ), flow_usdc_table_1 as ( select withdrawn.*, deposited.receiver, 'FLOW' as chain from flow_usdc_withdrawn withdrawn join flow_usdc_deposited deposited on withdrawn.tx_hash = deposited.tx_hash and withdrawn.transfer_amount = deposited.transfer_amount ), flow_usdc_table_2 as ( select withdrawn.*, deposited.receiver, 'FLOW' as chain from flow_usdc_withdrawn withdrawn join flow_usdc_deposited deposited on withdrawn.tx_hash = deposited.tx_hash and withdrawn.transfer_amount != deposited.transfer_amount where withdrawn.tx_hash not in (select tx_hash from flow_usdc_table_1) ), flow_usdc_transfers as ( select * from flow_usdc_table_1 union all select * from flow_usdc_table_2 ), ethereum_results as ( select chain as "Chain", case when transfer_amount >= 1000000 then 'More than 1,000,000 USDC' when transfer_amount >= 100000 and transfer_amount < 1000000 then 'Between 100,000 - 1,000,000 USDC' when transfer_amount >= 10000 and transfer_amount < 100000 then 'Between 10,000 - 100,000 USDC' when transfer_amount >= 1000 and transfer_amount < 10000 then 'Between 1,000 - 10,000 USDC' when transfer_amount >= 100 and transfer_amount < 1000 then 'Between 100 - 1,000 USDC' when transfer_amount >= 10 and transfer_amount < 100 then 'Between 10 - 100 USDC' when transfer_amount < 10 then 'Less than 10 USDC' end as "Distribution of Transfers", count(tx_hash) as "Number of Transfers", sum(transfer_amount) as "Amount of Transfers" from ethereum_usdc_transfers group by "Chain", "Distribution of Transfers" ), algorand_results as ( select chain as "Chain", case when transfer_amount >= 1000000 then 'More than 1,000,000 USDC' when transfer_amount >= 100000 and transfer_amount < 1000000 then 'Between 100,000 - 1,000,000 USDC' when transfer_amount >= 10000 and transfer_amount < 100000 then 'Between 10,000 - 100,000 USDC' when transfer_amount >= 1000 and transfer_amount < 10000 then 'Between 1,000 - 10,000 USDC' when transfer_amount >= 100 and transfer_amount < 1000 then 'Between 100 - 1,000 USDC' when transfer_amount >= 10 and transfer_amount < 100 then 'Between 10 - 100 USDC' when transfer_amount < 10 then 'Less than 10 USDC' end as "Distribution of Transfers", count(tx_hash) as "Number of Transfers", sum(transfer_amount) as "Amount of Transfers" from algorand_usdc_transfers group by "Chain", "Distribution of Transfers" ), solana_results as ( select chain as "Chain", case when transfer_amount >= 1000000 then 'More than 1,000,000 USDC' when transfer_amount >= 100000 and transfer_amount < 1000000 then 'Between 100,000 - 1,000,000 USDC' when transfer_amount >= 10000 and transfer_amount < 100000 then 'Between 10,000 - 100,000 USDC' when transfer_amount >= 1000 and transfer_amount < 10000 then 'Between 1,000 - 10,000 USDC' when transfer_amount >= 100 and transfer_amount < 1000 then 'Between 100 - 1,000 USDC' when transfer_amount >= 10 and transfer_amount < 100 then 'Between 10 - 100 USDC' when transfer_amount < 10 then 'Less than 10 USDC' end as "Distribution of Transfers", count(tx_hash) as "Number of Transfers", sum(transfer_amount) as "Amount of Transfers" from solana_usdc_transfers group by "Chain", "Distribution of Transfers" ), optimism_results as ( select chain as "Chain", case when transfer_amount >= 1000000 then 'More than 1,000,000 USDC' when transfer_amount >= 100000 and transfer_amount < 1000000 then 'Between 100,000 - 1,000,000 USDC' when transfer_amount >= 10000 and transfer_amount < 100000 then 'Between 10,000 - 100,000 USDC' when transfer_amount >= 1000 and transfer_amount < 10000 then 'Between 1,000 - 10,000 USDC' when transfer_amount >= 100 and transfer_amount < 1000 then 'Between 100 - 1,000 USDC' when transfer_amount >= 10 and transfer_amount < 100 then 'Between 10 - 100 USDC' when transfer_amount < 10 then 'Less than 10 USDC' end as "Distribution of Transfers", count(tx_hash) as "Number of Transfers", sum(transfer_amount) as "Amount of Transfers" from optimism_usdc_transfers group by "Chain", "Distribution of Transfers" ), osmosis_results as ( select chain as "Chain", case when transfer_amount >= 1000000 then 'More than 1,000,000 USDC' when transfer_amount >= 100000 and transfer_amount < 1000000 then 'Between 100,000 - 1,000,000 USDC' when transfer_amount >= 10000 and transfer_amount < 100000 then 'Between 10,000 - 100,000 USDC' when transfer_amount >= 1000 and transfer_amount < 10000 then 'Between 1,000 - 10,000 USDC' when transfer_amount >= 100 and transfer_amount < 1000 then 'Between 100 - 1,000 USDC' when transfer_amount >= 10 and transfer_amount < 100 then 'Between 10 - 100 USDC' when transfer_amount < 10 then 'Less than 10 USDC' end as "Distribution of Transfers", count(tx_hash) as "Number of Transfers", sum(transfer_amount) as "Amount of Transfers" from osmosis_usdc_transfers group by "Chain", "Distribution of Transfers" ), axelar_results as ( select chain as "Chain", case when transfer_amount >= 1000000 then 'More than 1,000,000 USDC' when transfer_amount >= 100000 and transfer_amount < 1000000 then 'Between 100,000 - 1,000,000 USDC' when transfer_amount >= 10000 and transfer_amount < 100000 then 'Between 10,000 - 100,000 USDC' when transfer_amount >= 1000 and transfer_amount < 10000 then 'Between 1,000 - 10,000 USDC' when transfer_amount >= 100 and transfer_amount < 1000 then 'Between 100 - 1,000 USDC' when transfer_amount >= 10 and transfer_amount < 100 then 'Between 10 - 100 USDC' when transfer_amount < 10 then 'Less than 10 USDC' end as "Distribution of Transfers", count(tx_hash) as "Number of Transfers", sum(transfer_amount) as "Amount of Transfers" from axelar_usdc_transfers group by "Chain", "Distribution of Transfers" ), near_results as ( select chain as "Chain", case when transfer_amount >= 1000000 then 'More than 1,000,000 USDC' when transfer_amount >= 100000 and transfer_amount < 1000000 then 'Between 100,000 - 1,000,000 USDC' when transfer_amount >= 10000 and transfer_amount < 100000 then 'Between 10,000 - 100,000 USDC' when transfer_amount >= 1000 and transfer_amount < 10000 then 'Between 1,000 - 10,000 USDC' when transfer_amount >= 100 and transfer_amount < 1000 then 'Between 100 - 1,000 USDC' when transfer_amount >= 10 and transfer_amount < 100 then 'Between 10 - 100 USDC' when transfer_amount < 10 then 'Less than 10 USDC' end as "Distribution of Transfers", count(tx_hash) as "Number of Transfers", sum(transfer_amount) as "Amount of Transfers" from near_usdc_transfers group by "Chain", "Distribution of Transfers" ), flow_results as ( select chain as "Chain", case when transfer_amount >= 1000000 then 'More than 1,000,000 USDC' when transfer_amount >= 100000 and transfer_amount < 1000000 then 'Between 100,000 - 1,000,000 USDC' when transfer_amount >= 10000 and transfer_amount < 100000 then 'Between 10,000 - 100,000 USDC' when transfer_amount >= 1000 and transfer_amount < 10000 then 'Between 1,000 - 10,000 USDC' when transfer_amount >= 100 and transfer_amount < 1000 then 'Between 100 - 1,000 USDC' when transfer_amount >= 10 and transfer_amount < 100 then 'Between 10 - 100 USDC' when transfer_amount < 10 then 'Less than 10 USDC' end as "Distribution of Transfers", count(tx_hash) as "Number of Transfers", sum(transfer_amount) as "Amount of Transfers" from flow_usdc_transfers group by "Chain", "Distribution of Transfers" ), final_result as ( select * from ethereum_results union all select * from algorand_results union all select * from solana_results union all select * from optimism_results union all select * from osmosis_results union all select * from axelar_results union all select * from near_results union all select * from flow_results ) select * from final_result where "Chain"='{{BLOCKCHAIN}}'

Analysis on USDC across multiple CHAINs