with ethereum_transfers as ( select 'ethereum' as blockchain, '1' discipline, case when amount < 10 then '1.Less than 10 USDC' when amount >= 10 and amount < 100 then '2.Between 10 - 100 USDC' when amount >= 100 and amount < 1000 then '3.Between 100 - 1,000 USDC' when amount >= 1000 and amount < 10000 then '4.Between 1,000 - 10,000 USDC' when amount >= 10000 and amount < 100000 then '5.Between 10,000 - 100,000 USDC' when amount >= 100000 and amount < 1000000 then '6.Between 100,000 - 1,000,000 USDC' when amount >= 1000000 then '7.More than 1,000,000 USDC' end as distribution_USDC_transfers, count(distinct tx_hash) as number_of_transfers from ethereum.core.ez_token_transfers where date(block_timestamp) >='{{startDate}}' and date(block_timestamp) <='{{endDate}}' and symbol='USDC' and amount > 0 group by 1,2,3 ) , solana_transfers as ( select 'solana' as blockchain, '2' discipline, case when amount < 10 then '1.Less than 10 USDC' when amount >= 10 and amount < 100 then '2.Between 10 - 100 USDC' when amount >= 100 and amount < 1000 then '3.Between 100 - 1,000 USDC' when amount >= 1000 and amount < 10000 then '4.Between 1,000 - 10,000 USDC' when amount >= 10000 and amount < 100000 then '5.Between 10,000 - 100,000 USDC' when amount >= 100000 and amount < 1000000 then '6.Between 100,000 - 1,000,000 USDC' when amount >= 1000000 then '7.More than 1,000,000 USDC' end as distribution_USDC_transfers, count(distinct tx_id) number_of_transfers from solana.core.fact_transfers where date(block_timestamp) >='{{startDate}}' and date(block_timestamp) <='{{endDate}}' and mint = 'EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v' and amount > 0 group by 1,2,3 ), algorand_transfers as ( select 'algorand' as blockchain, '3' discipline, case when amount < 10 then '1.Less than 10 USDC' when amount >= 10 and amount < 100 then '2.Between 10 - 100 USDC' when amount >= 100 and amount < 1000 then '3.Between 100 - 1,000 USDC' when amount >= 1000 and amount < 10000 then '4.Between 1,000 - 10,000 USDC' when amount >= 10000 and amount < 100000 then '5.Between 10,000 - 100,000 USDC' when amount >= 100000 and amount < 1000000 then '6.Between 100,000 - 1,000,000 USDC' when amount >= 1000000 then '7.More than 1,000,000 USDC' end as distribution_USDC_transfers, count(distinct tx_id) number_of_transfers from algorand.core.ez_transfer where date(block_timestamp) >='{{startDate}}' and date(block_timestamp) <='{{endDate}}' and asset_name= 'USDC' group by 1,2,3 ), flow_transfers as ( select 'flow' as blockchain, '4' discipline, case when (event_data:amount / 2) < 10 then '1.Less than 10 USDC' when (event_data:amount / 2) >= 10 and (event_data:amount / 2) < 100 then '2.Between 10 - 100 USDC' when (event_data:amount / 2) >= 100 and (event_data:amount / 2) < 1000 then '3.Between 100 - 1,000 USDC' when (event_data:amount / 2) >= 1000 and (event_data:amount / 2) < 10000 then '4.Between 1,000 - 10,000 USDC' when (event_data:amount / 2) >= 10000 and (event_data:amount / 2) < 100000 then '5.Between 10,000 - 100,000 USDC' when (event_data:amount / 2) >= 100000 and (event_data:amount / 2) < 1000000 then '6.Between 100,000 - 1,000,000 USDC' when (event_data:amount / 2) >= 1000000 then '7.More than 1,000,000 USDC' end as distribution_USDC_transfers, count(distinct tx_id) number_of_transfers from flow.core.fact_events where date(block_timestamp) >= '{{startDate}}' and date(block_timestamp) <= '{{endDate}}' and event_contract like 'A.b19436aae4d94622.FiatToken' and event_type in ('TokensDeposited','TokensWithdrawn') and event_data :from ::string <> 'null' and tx_succeeded = TRUE group by 1,2,3 ), near_transfers as ( select 'near' as blockchain, '5' discipline, case when (cast(SPLIT(tx:receipt[0]['outcome']['logs'], ' ')[1] as int)/power(10, 6)) < 10 then '1.Less than 10 USDC' when (cast(SPLIT(tx:receipt[0]['outcome']['logs'], ' ')[1] as int)/power(10, 6)) >= 10 and (cast(SPLIT(tx:receipt[0]['outcome']['logs'], ' ')[1] as int)/power(10, 6)) < 100 then '2.Between 10 - 100 USDC' when (cast(SPLIT(tx:receipt[0]['outcome']['logs'], ' ')[1] as int)/power(10, 6)) >= 100 and (cast(SPLIT(tx:receipt[0]['outcome']['logs'], ' ')[1] as int)/power(10, 6)) < 1000 then '3.Between 100 - 1,000 USDC' when (cast(SPLIT(tx:receipt[0]['outcome']['logs'], ' ')[1] as int)/power(10, 6)) >= 1000 and (cast(SPLIT(tx:receipt[0]['outcome']['logs'], ' ')[1] as int)/power(10, 6)) < 10000 then '4.Between 1,000 - 10,000 USDC' when (cast(SPLIT(tx:receipt[0]['outcome']['logs'], ' ')[1] as int)/power(10, 6)) >= 10000 and (cast(SPLIT(tx:receipt[0]['outcome']['logs'], ' ')[1] as int)/power(10, 6)) < 100000 then '5.Between 10,000 - 100,000 USDC' when (cast(SPLIT(tx:receipt[0]['outcome']['logs'], ' ')[1] as int)/power(10, 6)) >= 100000 and (cast(SPLIT(tx:receipt[0]['outcome']['logs'], ' ')[1] as int)/power(10, 6)) < 1000000 then '6.Between 100,000 - 1,000,000 USDC' when (cast(SPLIT(tx:receipt[0]['outcome']['logs'], ' ')[1] as int)/power(10, 6)) >= 1000000 then '7.More than 1,000,000 USDC' end as distribution_USDC_transfers, count(distinct tx_hash) number_of_transfers from near.core.fact_transactions inner join lateral flatten (input => tx :receipt) f where date(block_timestamp) >= '{{startDate}}' and date(block_timestamp) <= '{{endDate}}' and tx_receiver like 'a0b86991c6218b36c1d19d4a2e9eb0ce3606eb48.factory.bridge.near' and not SPLIT(tx:receipt[0]['outcome']['logs'], ' ')[1] like 'account' group by 1,2,3 ), optimism_transfers as ( select 'optimism' as blockchain, '6' discipline, case when (raw_amount/power(10,6)) < 10 then '1.Less than 10 USDC' when (raw_amount/power(10,6)) >= 10 and (raw_amount/power(10,6)) < 100 then '2.Between 10 - 100 USDC' when (raw_amount/power(10,6)) >= 100 and (raw_amount/power(10,6)) < 1000 then '3.Between 100 - 1,000 USDC' when (raw_amount/power(10,6)) >= 1000 and (raw_amount/power(10,6)) < 10000 then '4.Between 1,000 - 10,000 USDC' when (raw_amount/power(10,6)) >= 10000 and (raw_amount/power(10,6)) < 100000 then '5.Between 10,000 - 100,000 USDC' when (raw_amount/power(10,6)) >= 100000 and (raw_amount/power(10,6)) < 1000000 then '6.Between 100,000 - 1,000,000 USDC' when (raw_amount/power(10,6)) >= 1000000 then '7.More than 1,000,000 USDC' end as distribution_USDC_transfers, count(distinct tx_hash) as number_of_transfers FROM optimism.core.fact_token_transfers where date(block_timestamp) >= '{{startDate}}' and date(block_timestamp) <= '{{endDate}}' and contract_address='0x7f5c764cbc14f9669b88837ca1490cca17c31607' group by 1,2,3 ), osmosis_transfers as ( select 'osmosis' as blockchain, '7' discipline, case when (amount/power(10, decimal)) < 10 then '1.Less than 10 USDC' when (amount/power(10, decimal)) >= 10 and (amount/power(10, decimal)) < 100 then '2.Between 10 - 100 USDC' when (amount/power(10, decimal)) >= 100 and (amount/power(10, decimal)) < 1000 then '3.Between 100 - 1,000 USDC' when (amount/power(10, decimal)) >= 1000 and (amount/power(10, decimal)) < 10000 then '4.Between 1,000 - 10,000 USDC' when (amount/power(10, decimal)) >= 10000 and (amount/power(10, decimal)) < 100000 then '5.Between 10,000 - 100,000 USDC' when (amount/power(10, decimal)) >= 100000 and (amount/power(10, decimal)) < 1000000 then '6.Between 100,000 - 1,000,000 USDC' when (amount/power(10, decimal)) >= 1000000 then '7.More than 1,000,000 USDC' end as distribution_USDC_transfers, count(distinct tx_id) as number_of_transfers FROM osmosis.core.fact_transfers WHERE date(block_timestamp) >= '{{startDate}}' and date(block_timestamp) <= '{{endDate}}' and currency like 'ibc/D189335C6E4A68B513C10AB227BF1C1D38C746766278BA3EEB4FB14124F1D858' group by 1,2,3 ), axelar_transfers as ( select 'axelar' as blockchain, '8' discipline, case when (amount/power(10,6)) < 10 then '1.Less than 10 USDC' when (amount/power(10,6)) >= 10 and (amount/power(10,6)) < 100 then '2.Between 10 - 100 USDC' when (amount/power(10,6)) >= 100 and (amount/power(10,6)) < 1000 then '3.Between 100 - 1,000 USDC' when (amount/power(10,6)) >= 1000 and (amount/power(10,6)) < 10000 then '4.Between 1,000 - 10,000 USDC' when (amount/power(10,6)) >= 10000 and (amount/power(10,6)) < 100000 then '5.Between 10,000 - 100,000 USDC' when (amount/power(10,6)) >= 100000 and (amount/power(10,6)) < 1000000 then '6.Between 100,000 - 1,000,000 USDC' when (amount/power(10,6)) >= 1000000 then '7.More than 1,000,000 USDC' end as distribution_USDC_transfers, count(distinct tx_id) as number_of_transfers from axelar.core.fact_transfers where date(block_timestamp) >= '{{startDate}}' and date(block_timestamp) <= '{{endDate}}' and currency like 'uusdc' group by 1,2,3 ) select * from ethereum_transfers union select * from solana_transfers union select * from algorand_transfers union select * from flow_transfers union select * from near_transfers union select * from optimism_transfers union select * from osmosis_transfers union select * from axelar_transfers order by 3,2

Flipside World Cup Round of 32: USDC Transfers