WITH GET_INPUT_Timespan AS ( SELECT {{Timespan}} AS INPUT_Timespan, CASE WHEN INPUT_Timespan = 'Last 30 days' THEN CURRENT_DATE - 30 WHEN INPUT_Timespan = 'Last 60 days' THEN CURRENT_DATE - 60 WHEN INPUT_Timespan = 'Last 90 days' THEN CURRENT_DATE - 90 WHEN INPUT_Timespan = 'Last 180 days' THEN CURRENT_DATE - 180 ELSE '2014-01-01' END AS Timespan ), ----------------------------------------------------Ethereum_USDC_Transfers----------------------------------------------- Ethereum_USDC_Transfers AS ( SELECT BLOCK_TIMESTAMP, TX_HASH AS TX, FROM_ADDRESS AS SENDER, TO_ADDRESS AS RECEIVER, RAW_AMOUNT/POW(10,6) AS AMOUNT, 'Ethereum' AS Chain FROM ethereum.core.ez_token_transfers WHERE CONTRACT_ADDRESS = '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48' AND RAW_AMOUNT > 0 AND RAW_AMOUNT IS NOT NULL AND BLOCK_TIMESTAMP::DATE >= (SELECT Timespan FROM GET_INPUT_Timespan) AND FROM_ADDRESS != '0x0000000000000000000000000000000000000000' AND --Mint TO_ADDRESS != '0x0000000000000000000000000000000000000000' --Burn ), Ethereum AS ( SELECT COUNT(*), CASE WHEN AMOUNT < 10 THEN 'Less than 10$ USDC transfer' WHEN AMOUNT >= 10 AND AMOUNT < 100 THEN 'Between 10$ and 100$ USDC transfer' WHEN AMOUNT >= 100 AND AMOUNT < 500 THEN 'Between 100$ and 500$ USDC transfer' WHEN AMOUNT >= 500 AND AMOUNT < 1000 THEN 'Between 500$ and 1000$ USDC transfer' WHEN AMOUNT >= 1000 AND AMOUNT < 2000 THEN 'Between 1000$ and 2000$ USDC transfer' WHEN AMOUNT >= 2000 AND AMOUNT < 5000 THEN 'Between 2000$ and 5000$ USDC transfer' WHEN AMOUNT >= 5000 AND AMOUNT < 10000 THEN 'Between 5000$ and 10000$ USDC transfer' WHEN AMOUNT >= 10000 AND AMOUNT < 100000 THEN 'Between 10000$ and 100000$ USDC transfer' WHEN AMOUNT >= 100000 THEN 'More than 100000$ USDC transfer' END AS DISTRO FROM Ethereum_USDC_Transfers GROUP BY DISTRO ), ----------------------------------------------------Solana_USDC_Transfers----------------------------------------------- Solana_USDC_Transfers AS ( SELECT BLOCK_TIMESTAMP, TX_ID AS TX, TX_FROM AS SENDER, TX_TO AS RECEIVER, AMOUNT, 'Solana' AS Chain FROM solana.core.fact_transfers WHERE MINT = 'EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v' AND BLOCK_TIMESTAMP::DATE >= (SELECT Timespan FROM GET_INPUT_Timespan) AND AMOUNT > 0 AND AMOUNT IS NOT NULL ), Solana AS ( SELECT COUNT(*), CASE WHEN AMOUNT < 10 THEN 'Less than 10$ USDC transfer' WHEN AMOUNT >= 10 AND AMOUNT < 100 THEN 'Between 10$ and 100$ USDC transfer' WHEN AMOUNT >= 100 AND AMOUNT < 500 THEN 'Between 100$ and 500$ USDC transfer' WHEN AMOUNT >= 500 AND AMOUNT < 1000 THEN 'Between 500$ and 1000$ USDC transfer' WHEN AMOUNT >= 1000 AND AMOUNT < 2000 THEN 'Between 1000$ and 2000$ USDC transfer' WHEN AMOUNT >= 2000 AND AMOUNT < 5000 THEN 'Between 2000$ and 5000$ USDC transfer' WHEN AMOUNT >= 5000 AND AMOUNT < 10000 THEN 'Between 5000$ and 10000$ USDC transfer' WHEN AMOUNT >= 10000 AND AMOUNT < 100000 THEN 'Between 10000$ and 100000$ USDC transfer' WHEN AMOUNT >= 100000 THEN 'More than 100000$ USDC transfer' END AS DISTRO FROM Solana_USDC_Transfers GROUP BY DISTRO ), ----------------------------------------------------Algorand_USDC_Transfers----------------------------------------------- Algorand_USDC_Transfers AS ( SELECT BLOCK_TIMESTAMP, TX_ID AS TX, TX_SENDER AS SENDER, RECEIVER, AMOUNT, 'Algorand' AS Chain FROM algorand.core.ez_transfer WHERE ASSET_ID = 31566704 AND BLOCK_TIMESTAMP::DATE >= (SELECT Timespan FROM GET_INPUT_Timespan) AND AMOUNT > 0 AND AMOUNT IS NOT NULL ), Algorand AS ( SELECT COUNT(*), CASE WHEN AMOUNT < 10 THEN 'Less than 10$ USDC transfer' WHEN AMOUNT >= 10 AND AMOUNT < 100 THEN 'Between 10$ and 100$ USDC transfer' WHEN AMOUNT >= 100 AND AMOUNT < 500 THEN 'Between 100$ and 500$ USDC transfer' WHEN AMOUNT >= 500 AND AMOUNT < 1000 THEN 'Between 500$ and 1000$ USDC transfer' WHEN AMOUNT >= 1000 AND AMOUNT < 2000 THEN 'Between 1000$ and 2000$ USDC transfer' WHEN AMOUNT >= 2000 AND AMOUNT < 5000 THEN 'Between 2000$ and 5000$ USDC transfer' WHEN AMOUNT >= 5000 AND AMOUNT < 10000 THEN 'Between 5000$ and 10000$ USDC transfer' WHEN AMOUNT >= 10000 AND AMOUNT < 100000 THEN 'Between 10000$ and 100000$ USDC transfer' WHEN AMOUNT >= 100000 THEN 'More than 100000$ USDC transfer' END AS DISTRO FROM Algorand_USDC_Transfers GROUP BY DISTRO ), ----------------------------------------------------AXELAR_USDC_Transfers----------------------------------------------- Axelar_USDC_TRASNFERS AS ( SELECT BLOCK_TIMESTAMP, TX_ID AS TX, SENDER, RECEIVER, AMOUNT/POW(10,DECIMAL) AS AMOUNT, 'Axelar' AS Chain FROM axelar.core.fact_transfers WHERE TX_SUCCEEDED = 'TRUE' AND BLOCK_TIMESTAMP::DATE >= (SELECT Timespan FROM GET_INPUT_Timespan) AND AMOUNT>0 AND AMOUNT IS NOT NULL AND CURRENCY IN ('uusdc') AND TX_SUCCEEDED = 'TRUE'--AND TRANSFER_TYPE IN ('AXELAR', 'IBC_TRANSFER_IN', 'IBC_TRANSFER_IN') ), Axelar AS ( SELECT COUNT(*), CASE WHEN AMOUNT < 10 THEN 'Less than 10$ USDC transfer' WHEN AMOUNT >= 10 AND AMOUNT < 100 THEN 'Between 10$ and 100$ USDC transfer' WHEN AMOUNT >= 100 AND AMOUNT < 500 THEN 'Between 100$ and 500$ USDC transfer' WHEN AMOUNT >= 500 AND AMOUNT < 1000 THEN 'Between 500$ and 1000$ USDC transfer' WHEN AMOUNT >= 1000 AND AMOUNT < 2000 THEN 'Between 1000$ and 2000$ USDC transfer' WHEN AMOUNT >= 2000 AND AMOUNT < 5000 THEN 'Between 2000$ and 5000$ USDC transfer' WHEN AMOUNT >= 5000 AND AMOUNT < 10000 THEN 'Between 5000$ and 10000$ USDC transfer' WHEN AMOUNT >= 10000 AND AMOUNT < 100000 THEN 'Between 10000$ and 100000$ USDC transfer' WHEN AMOUNT >= 100000 THEN 'More than 100000$ USDC transfer' END AS DISTRO FROM Axelar_USDC_TRASNFERS GROUP BY DISTRO ), ----------------------------------------------------OSMOSIS_USDC_Transfers----------------------------------------------- OSMOSIS_USDC_TRASNFERS AS ( SELECT BLOCK_TIMESTAMP, TX_ID AS TX, SENDER, RECEIVER, AMOUNT/POW(10,DECIMAL) AS AMOUNT, 'Osmosis' AS Chain FROM osmosis.core.fact_transfers WHERE TX_STATUS = 'SUCCEEDED' AND BLOCK_TIMESTAMP::DATE >= (SELECT Timespan FROM GET_INPUT_Timespan) AND AMOUNT>0 AND AMOUNT IS NOT NULL AND CURRENCY IN ('uusdc', 'ibc/D189335C6E4A68B513C10AB227BF1C1D38C746766278BA3EEB4FB14124F1D858') AND TX_STATUS = 'SUCCEEDED' --AND TRANSFER_TYPE IN ('OSMOSIS', 'IBC_TRANSFER_IN', 'IBC_TRANSFER_IN') ), Osmosis AS ( SELECT COUNT(*), CASE WHEN AMOUNT < 10 THEN 'Less than 10$ USDC transfer' WHEN AMOUNT >= 10 AND AMOUNT < 100 THEN 'Between 10$ and 100$ USDC transfer' WHEN AMOUNT >= 100 AND AMOUNT < 500 THEN 'Between 100$ and 500$ USDC transfer' WHEN AMOUNT >= 500 AND AMOUNT < 1000 THEN 'Between 500$ and 1000$ USDC transfer' WHEN AMOUNT >= 1000 AND AMOUNT < 2000 THEN 'Between 1000$ and 2000$ USDC transfer' WHEN AMOUNT >= 2000 AND AMOUNT < 5000 THEN 'Between 2000$ and 5000$ USDC transfer' WHEN AMOUNT >= 5000 AND AMOUNT < 10000 THEN 'Between 5000$ and 10000$ USDC transfer' WHEN AMOUNT >= 10000 AND AMOUNT < 100000 THEN 'Between 10000$ and 100000$ USDC transfer' WHEN AMOUNT >= 100000 THEN 'More than 100000$ USDC transfer' END AS DISTRO FROM OSMOSIS_USDC_TRASNFERS GROUP BY DISTRO ), ----------------------------------------------------FLOW_USDC_Transfers----------------------------------------------- Find_Sender AS ( SELECT BLOCK_TIMESTAMP, TX_ID AS TX, EVENT_DATA:from AS SENDER, (EVENT_DATA:amount)::float AS AMOUNT FROM flow.core.fact_events WHERE EVENT_TYPE = 'TokensWithdrawn' AND EVENT_CONTRACT = 'A.b19436aae4d94622.FiatToken' AND SENDER IS NOT NULL and SENDER like '0x%' AND TX_SUCCEEDED = 'TRUE' AND TX_SUCCEEDED = 'TRUE' AND BLOCK_TIMESTAMP::DATE >= (SELECT Timespan FROM GET_INPUT_Timespan) ), Find_RECEIVER AS ( SELECT BLOCK_TIMESTAMP, TX_ID AS TX, EVENT_DATA:to AS RECEIVER, (EVENT_DATA:amount)::float AS AMOUNT FROM flow.core.fact_events WHERE EVENT_TYPE = 'TokensDeposited' AND EVENT_CONTRACT = 'A.b19436aae4d94622.FiatToken' AND RECEIVER IS NOT NULL AND RECEIVER like '0x%' ), Send_To_One_User AS ( SELECT Find_Sender.BLOCK_TIMESTAMP AS BLOCK_TIMESTAMP , Find_Sender.TX AS TX, SENDER, RECEIVER, Find_Sender.AMOUNT AS AMOUNT, 'Flow' AS Chain FROM Find_Sender JOIN Find_RECEIVER ON (Find_Sender.TX = Find_RECEIVER.TX AND Find_Sender.Amount = Find_RECEIVER. Amount) ), Send_To_Multiple_Users AS ( SELECT Find_RECEIVER.BLOCK_TIMESTAMP AS BLOCK_TIMESTAMP, Find_RECEIVER.TX AS TX, SENDER, RECEIVER, Find_RECEIVER.AMOUNT AS AMOUNT , 'Flow' AS Chain FROM Find_Sender JOIN Find_RECEIVER ON (Find_Sender.TX = Find_RECEIVER.TX AND Find_Sender.Amount != Find_RECEIVER. Amount) WHERE Find_RECEIVER.TX NOT IN (SELECT TX FROM Send_To_One_User) ), Flow_USDC_Transfers AS ( SELECT * FROM Send_To_One_User UNION ALL SELECT * FROM Send_To_Multiple_Users ), Flow AS ( SELECT COUNT(*), CASE WHEN AMOUNT < 10 THEN 'Less than 10$ USDC transfer' WHEN AMOUNT >= 10 AND AMOUNT < 100 THEN 'Between 10$ and 100$ USDC transfer' WHEN AMOUNT >= 100 AND AMOUNT < 500 THEN 'Between 100$ and 500$ USDC transfer' WHEN AMOUNT >= 500 AND AMOUNT < 1000 THEN 'Between 500$ and 1000$ USDC transfer' WHEN AMOUNT >= 1000 AND AMOUNT < 2000 THEN 'Between 1000$ and 2000$ USDC transfer' WHEN AMOUNT >= 2000 AND AMOUNT < 5000 THEN 'Between 2000$ and 5000$ USDC transfer' WHEN AMOUNT >= 5000 AND AMOUNT < 10000 THEN 'Between 5000$ and 10000$ USDC transfer' WHEN AMOUNT >= 10000 AND AMOUNT < 100000 THEN 'Between 10000$ and 100000$ USDC transfer' WHEN AMOUNT >= 100000 THEN 'More than 100000$ USDC transfer' END AS DISTRO FROM Flow_USDC_Transfers GROUP BY DISTRO ), ----------------------------------------------------Near_USDC_Transfers----------------------------------------------- Near_USDC_TRASNFERS AS ( SELECT BLOCK_TIMESTAMP,TX_HASH AS TX, SPLIT(logs[0], ' ')[3]::string as SENDER, split(logs[0], ' ')[5]::string as RECEIVER, (split(logs[0], ' ')[1] / pow(10, 6))::float AS 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 >= (SELECT Timespan FROM GET_INPUT_Timespan) ), Near AS ( SELECT COUNT(*), CASE WHEN AMOUNT < 10 THEN 'Less than 10$ USDC transfer' WHEN AMOUNT >= 10 AND AMOUNT < 100 THEN 'Between 10$ and 100$ USDC transfer' WHEN AMOUNT >= 100 AND AMOUNT < 500 THEN 'Between 100$ and 500$ USDC transfer' WHEN AMOUNT >= 500 AND AMOUNT < 1000 THEN 'Between 500$ and 1000$ USDC transfer' WHEN AMOUNT >= 1000 AND AMOUNT < 2000 THEN 'Between 1000$ and 2000$ USDC transfer' WHEN AMOUNT >= 2000 AND AMOUNT < 5000 THEN 'Between 2000$ and 5000$ USDC transfer' WHEN AMOUNT >= 5000 AND AMOUNT < 10000 THEN 'Between 5000$ and 10000$ USDC transfer' WHEN AMOUNT >= 10000 AND AMOUNT < 100000 THEN 'Between 10000$ and 100000$ USDC transfer' WHEN AMOUNT >= 100000 THEN 'More than 100000$ USDC transfer' END AS DISTRO FROM Near_USDC_TRASNFERS GROUP BY DISTRO ), ----------------------------------------------------Optimism_USDC_Transfers----------------------------------------------- Optimism_USDC_Transfers AS ( SELECT BLOCK_TIMESTAMP, TX_HASH AS TX, FROM_ADDRESS AS SENDER, TO_ADDRESS AS RECEIVER, RAW_AMOUNT/POW(10,6) AS AMOUNT, 'Optimism' AS Chain FROM optimism.core.fact_token_transfers WHERE CONTRACT_ADDRESS = '0x7f5c764cbc14f9669b88837ca1490cca17c31607' AND BLOCK_TIMESTAMP::DATE >= (SELECT Timespan FROM GET_INPUT_Timespan) AND RAW_AMOUNT > 0 AND RAW_AMOUNT IS NOT NULL --AND FROM_ADDRESS = '0x0000000000000000000000000000000000000000' -- Inflow on bridges --TO_ADDRESS = '0x0000000000000000000000000000000000000000' -- Outflow on bridges ), Optimism AS ( SELECT COUNT(*), CASE WHEN AMOUNT < 10 THEN 'Less than 10$ USDC transfer' WHEN AMOUNT >= 10 AND AMOUNT < 100 THEN 'Between 10$ and 100$ USDC transfer' WHEN AMOUNT >= 100 AND AMOUNT < 500 THEN 'Between 100$ and 500$ USDC transfer' WHEN AMOUNT >= 500 AND AMOUNT < 1000 THEN 'Between 500$ and 1000$ USDC transfer' WHEN AMOUNT >= 1000 AND AMOUNT < 2000 THEN 'Between 1000$ and 2000$ USDC transfer' WHEN AMOUNT >= 2000 AND AMOUNT < 5000 THEN 'Between 2000$ and 5000$ USDC transfer' WHEN AMOUNT >= 5000 AND AMOUNT < 10000 THEN 'Between 5000$ and 10000$ USDC transfer' WHEN AMOUNT >= 10000 AND AMOUNT < 100000 THEN 'Between 10000$ and 100000$ USDC transfer' WHEN AMOUNT >= 100000 THEN 'More than 100000$ USDC transfer' END AS DISTRO FROM Optimism_USDC_Transfers GROUP BY DISTRO) SELECT * FROM {{Select_a_blockchain_for_deeper_analysis}} -- SELECT * FROM Flow_USDC_Transfers WHERE amount > 100000 LIMIT 20

World Cup of Bounties Flipside World Cup Round of 32: USDC Transfers