--USDC sample , decimal 6 ---SELECT * FROM ETHEREUM.CORE.FACT_EVENT_LOGS WHERE TX_HASH='0xd98f1397e1c761b2b59423fb9e6e01096318fbdc0e60019de1f918d14c5c4b4d' --USDT sample , decimal 6 ---SELECT * FROM ETHEREUM.CORE.FACT_EVENT_LOGS WHERE TX_HASH='0xe21172c4243b49901f35c7eb8f7c87e6d09dc4b216a788fba8badae06de1570c' --MATIC sample, decimal 18 ---SELECT * FROM ETHEREUM.CORE.FACT_EVENT_LOGS WHERE TX_HASH='0x8d7bac12c7e70bad3ad2a6502f50f37f81de8724e80ef205b9d763c91fb47d94' --DAI sample, decimal 18 ---SELECT * FROM ETHEREUM.CORE.FACT_EVENT_LOGS WHERE TX_HASH='0xcb381fcbe769b0cccc2b5030f8b9120c44d3bcfd2c4a0474e8510b5c143299f7' --ETH sample, decimal 18 ---SELECT * FROM ETHEREUM.CORE.FACT_EVENT_LOGS WHERE TX_HASH='0x5aa4ee8dbcbbdbceabeacbee3f2ae0115f0b998a3290fc501f34af6361b91816' --WBTC sample, decimal 8 ---SELECT * FROM ETHEREUM.CORE.FACT_EVENT_LOGS WHERE TX_HASH='0x0b6729270e5fe2829f02a61bc8a1a26398819eecbef79887de9d47a193b5e9ab' --ChainIds: --Arbitrum chain ID=42161 --Optimism chain ID= 10 --Polygon chain ID=137 --GNOSIS chain ID = 100 --Addresses acquired from https://github.com/hop-protocol/hop/blob/develop/packages/core/src/addresses/mainnet.ts ---ethereum --USDC contract address: 0x3666f603Cc164936C1b87e207F36BEBa4AC5f18a --USDT contract address: 0x3E4a3a4796d16c0Cd582C382691998f7c06420B6 --MATIC contract address: 0x22B1Cbb8D98a01a3B71D034BB899775A76Eb1cc2 --DAI contract address: 0x3d4Cc8A61c7528Fd86C55cfe061a78dCBA48EDd1 --ETH contract address: 0xb8901acB165ed027E32754E0FFe830802919727f --WBTC contract address: 0xb98454270065A31D71Bf635F6F7Ee6A518dFb849 --MATIC daily price WITH MATIC_DAILY_PRICE AS( SELECT DATE_TRUNC('day',HOUR) as date, AVG(PRICE) as PRICE FROM ETHEREUM.CORE.FACT_HOURLY_TOKEN_PRICES WHERE SYMBOL='MATIC' GROUP BY date ), WBTC_DAILY_PRICE AS( SELECT DATE_TRUNC('day',HOUR) as date, AVG(PRICE) as PRICE FROM ETHEREUM.CORE.FACT_HOURLY_TOKEN_PRICES WHERE SYMBOL='WBTC' GROUP BY date ), ETH_DAILY_PRICE AS( SELECT DATE_TRUNC('day',BLOCK_TIMESTAMP) as date, AVG(AMOUNT_USD/AMOUNT) as PRICE FROM ETHEREUM.CORE.EZ_ETH_TRANSFERS GROUP BY date ), --USDC data USDC_Data AS( SELECT DATE_TRUNC('day', BLOCK_TIMESTAMP) as date, ORIGIN_FROM_ADDRESS as user_address, EVENT_INPUTS:amount/pow(10, 6) as amount_usd, EVENT_INPUTS:chainId as chainId FROM ETHEREUM.CORE.FACT_EVENT_LOGS WHERE EVENT_NAME='TransferSentToL2' AND ORIGIN_TO_ADDRESS=lower('0x3666f603Cc164936C1b87e207F36BEBa4AC5f18a') AND TX_STATUS='SUCCESS' ), --USDT data USDT_Data AS( SELECT DATE_TRUNC('day', BLOCK_TIMESTAMP) as date, ORIGIN_FROM_ADDRESS as user_address, EVENT_INPUTS:amount/pow(10, 6) as amount_usd, EVENT_INPUTS:chainId as chainId FROM ETHEREUM.CORE.FACT_EVENT_LOGS WHERE EVENT_NAME='TransferSentToL2' AND ORIGIN_TO_ADDRESS=lower('0x3E4a3a4796d16c0Cd582C382691998f7c06420B6') AND TX_STATUS='SUCCESS' ), --DAI data DAI_Data AS( SELECT DATE_TRUNC('day', BLOCK_TIMESTAMP) as date, ORIGIN_FROM_ADDRESS as user_address, EVENT_INPUTS:amount/pow(10, 18) as amount_usd, EVENT_INPUTS:chainId as chainId FROM ETHEREUM.CORE.FACT_EVENT_LOGS WHERE EVENT_NAME='TransferSentToL2' AND ORIGIN_TO_ADDRESS=lower('0x3d4Cc8A61c7528Fd86C55cfe061a78dCBA48EDd1') AND TX_STATUS='SUCCESS' ), MATIC_DATA_TEMP AS( SELECT DATE_TRUNC('day', BLOCK_TIMESTAMP) as date,ORIGIN_FROM_ADDRESS as user_address, EVENT_INPUTS:amount/pow(10, 18) as amount_matic, EVENT_INPUTS:chainId as chainId FROM ETHEREUM.CORE.FACT_EVENT_LOGS WHERE EVENT_NAME='TransferSentToL2' AND ORIGIN_TO_ADDRESS=lower('0x22B1Cbb8D98a01a3B71D034BB899775A76Eb1cc2') AND TX_STATUS='SUCCESS' ), MATIC_DATA_WITH_PRICE AS( SELECT MATIC_DATA_TEMP.*, MATIC_DAILY_PRICE.PRICE AS PRICE FROM MATIC_DATA_TEMP INNER JOIN MATIC_DAILY_PRICE ON MATIC_DATA_TEMP.date = MATIC_DAILY_PRICE.date ), MATIC_DATA AS( SELECT date, user_address, amount_matic*PRICE as amount_usd, chainId FROM MATIC_DATA_WITH_PRICE ), ETH_DATA_TEMP AS( SELECT DATE_TRUNC('day', BLOCK_TIMESTAMP) as date,ORIGIN_FROM_ADDRESS as user_address, EVENT_INPUTS:amount/pow(10, 18) as amount_ETH, EVENT_INPUTS:chainId as chainId FROM ETHEREUM.CORE.FACT_EVENT_LOGS WHERE EVENT_NAME='TransferSentToL2' AND ORIGIN_TO_ADDRESS=lower('0xb8901acB165ed027E32754E0FFe830802919727f') AND TX_STATUS='SUCCESS' ), ETH_DATA_WITH_PRICE AS( SELECT ETH_DATA_TEMP.*, ETH_DAILY_PRICE.PRICE AS PRICE FROM ETH_DATA_TEMP INNER JOIN ETH_DAILY_PRICE ON ETH_DATA_TEMP.date = ETH_DAILY_PRICE.date ), ETH_DATA AS( SELECT date, user_address, amount_ETH*PRICE as amount_usd, chainId FROM ETH_DATA_WITH_PRICE ), WBTC_DATA_TEMP AS( SELECT DATE_TRUNC('day', BLOCK_TIMESTAMP) as date,ORIGIN_FROM_ADDRESS as user_address, EVENT_INPUTS:amount/pow(10, 8) as amount_WBTC, EVENT_INPUTS:chainId as chainId FROM ETHEREUM.CORE.FACT_EVENT_LOGS WHERE EVENT_NAME='TransferSentToL2' AND ORIGIN_TO_ADDRESS=lower('0xb98454270065A31D71Bf635F6F7Ee6A518dFb849') AND TX_STATUS='SUCCESS' ), WBTC_DATA_WITH_PRICE AS( SELECT WBTC_DATA_TEMP.*, WBTC_DAILY_PRICE.PRICE AS PRICE FROM WBTC_DATA_TEMP INNER JOIN WBTC_DAILY_PRICE ON WBTC_DATA_TEMP.date = WBTC_DAILY_PRICE.date ), WBTC_DATA AS( SELECT date, user_address, amount_WBTC*PRICE as amount_usd, chainId FROM WBTC_DATA_WITH_PRICE ), All_TOKEN_DATA AS( SELECT date, amount_usd, chainId FROM USDC_DATA UNION SELECT date, amount_usd, chainId FROM USDT_DATA UNION SELECT date, amount_usd, chainId FROM DAI_DATA UNION SELECT date, amount_usd, chainId FROM MATIC_DATA UNION SELECT date, amount_usd, chainId FROM ETH_DATA UNION SELECT date, amount_usd, chainId FROM WBTC_DATA ), To_Optimism AS( SELECT date, amount_usd, coalesce(chainId, 0) as chainIdNull from All_TOKEN_DATA WHERE chainIdNull='10' AND date >= '2022-07-01' and date <= '2022-07-31' ), To_arbitrum AS( SELECT date, amount_usd, coalesce(chainId, 0) as chainIdNull from All_TOKEN_DATA WHERE chainIdNull='42161' AND date >= '2022-07-01' and date <= '2022-07-31' ), --function name: swap and send --event_name: tokenswap --calculate outflow --optimism contract addresses: --usdc: 0x2ad09850b0CA4c7c1B33f5AcD6cBAbCaB5d6e796 --usdt: 0x7D269D3E0d61A05a0bA976b7DBF8805bF844AF3F --dai: 0xb3C68a491608952Cb1257FC9909a537a0173b63B --eth: 0x86cA30bEF97fB651b8d866D45503684b90cb3312 --wbtc: 0x2A11a98e2fCF4674F30934B5166645fE6CA35F56 --USDC data USDC_Data_outflow AS( SELECT DATE_TRUNC('day', BLOCK_TIMESTAMP) as date, ORIGIN_FROM_ADDRESS as user_address, EVENT_INPUTS:tokensBought/pow(10, 6) as amount_usd FROM optimism.CORE.FACT_EVENT_LOGS WHERE EVENT_NAME='TokenSwap' AND ORIGIN_TO_ADDRESS=lower('0x2ad09850b0CA4c7c1B33f5AcD6cBAbCaB5d6e796') AND TX_STATUS='SUCCESS' ), USDT_Data_outflow AS( SELECT DATE_TRUNC('day', BLOCK_TIMESTAMP) as date, ORIGIN_FROM_ADDRESS as user_address, EVENT_INPUTS:tokensBought/pow(10, 6) as amount_usd FROM optimism.CORE.FACT_EVENT_LOGS WHERE EVENT_NAME='TokenSwap' AND ORIGIN_TO_ADDRESS=lower('0x7D269D3E0d61A05a0bA976b7DBF8805bF844AF3F') AND TX_STATUS='SUCCESS' ), DAI_Data_outflow AS( SELECT DATE_TRUNC('day', BLOCK_TIMESTAMP) as date, ORIGIN_FROM_ADDRESS as user_address, EVENT_INPUTS:tokensBought/pow(10, 18) as amount_usd FROM optimism.CORE.FACT_EVENT_LOGS WHERE EVENT_NAME='TokenSwap' AND ORIGIN_TO_ADDRESS=lower('0xb3C68a491608952Cb1257FC9909a537a0173b63B') AND TX_STATUS='SUCCESS' ), ETH_DATA_TEMP_outflow AS( SELECT DATE_TRUNC('day', BLOCK_TIMESTAMP) as date,ORIGIN_FROM_ADDRESS as user_address, EVENT_INPUTS:tokensBought/pow(10, 18) as amount_ETH FROM optimism.CORE.FACT_EVENT_LOGS WHERE EVENT_NAME='TokenSwap' AND ORIGIN_TO_ADDRESS=lower('0x86cA30bEF97fB651b8d866D45503684b90cb3312') AND TX_STATUS='SUCCESS' ), ETH_DATA_WITH_PRICE_outflow AS( SELECT ETH_DATA_TEMP_outflow.*, ETH_DAILY_PRICE.PRICE AS PRICE FROM ETH_DATA_TEMP_outflow INNER JOIN ETH_DAILY_PRICE ON ETH_DATA_TEMP_outflow.date = ETH_DAILY_PRICE.date ), ETH_DATA_outflow AS( SELECT date, user_address, amount_ETH*PRICE as amount_usd FROM ETH_DATA_WITH_PRICE_outflow ), WBTC_DATA_TEMP_outflow AS( SELECT DATE_TRUNC('day', BLOCK_TIMESTAMP) as date,ORIGIN_FROM_ADDRESS as user_address, EVENT_INPUTS:tokensBought/pow(10, 8) as amount_WBTC FROM optimism.CORE.FACT_EVENT_LOGS WHERE EVENT_NAME='TokenSwap' AND ORIGIN_TO_ADDRESS=lower('0x2A11a98e2fCF4674F30934B5166645fE6CA35F56') AND TX_STATUS='SUCCESS' ), WBTC_DATA_WITH_PRICE_outflow AS( SELECT WBTC_DATA_TEMP_outflow.*, WBTC_DAILY_PRICE.PRICE AS PRICE FROM WBTC_DATA_TEMP_outflow INNER JOIN WBTC_DAILY_PRICE ON WBTC_DATA_TEMP_outflow.date = WBTC_DAILY_PRICE.date ), WBTC_DATA_outflow AS( SELECT date, user_address, amount_WBTC*PRICE as amount_usd FROM WBTC_DATA_WITH_PRICE_outflow ), All_TOKEN_DATA_OUTFLOW AS( SELECT date, amount_usd FROM USDC_DATA_outflow where date >= '2022-07-01' and date <= '2022-07-31' UNION SELECT date, amount_usd FROM USDT_DATA_outflow where date >= '2022-07-01' and date <= '2022-07-31' UNION SELECT date, amount_usd FROM DAI_DATA_outflow where date >= '2022-07-01' and date <= '2022-07-31' UNION SELECT date, amount_usd FROM ETH_DATA_outflow where date >= '2022-07-01' and date <= '2022-07-31' UNION SELECT date, amount_usd FROM WBTC_DATA_outflow where date >= '2022-07-01' and date <= '2022-07-31' ), inflow_outflow_date as( select To_Optimism.date as date, To_Optimism.amount_usd as inflow_usd, All_TOKEN_DATA_OUTFLOW.amount_usd as outflow_usd from To_Optimism inner join All_TOKEN_DATA_OUTFLOW on To_Optimism.date = All_TOKEN_DATA_OUTFLOW.date ), --arbitrum contract addresses: --usdc: 0xe22D2beDb3Eca35E6397e0C6D62857094aA26F52 --usdt: 0xCB0a4177E0A60247C0ad18Be87f8eDfF6DD30283 --dai: 0xe7F40BF16AB09f4a6906Ac2CAA4094aD2dA48Cc2 --eth: 0x33ceb27b39d2Bb7D2e61F7564d3Df29344020417 --wbtc: 0xC08055b634D43F2176d721E26A3428D3b7E7DdB5 --USDC data USDC_Data_outflow_arbi AS( SELECT DATE_TRUNC('day', BLOCK_TIMESTAMP) as date, ORIGIN_FROM_ADDRESS as user_address, EVENT_INPUTS:tokensBought/pow(10, 6) as amount_usd FROM arbitrum.CORE.FACT_EVENT_LOGS WHERE EVENT_NAME='TokenSwap' AND ORIGIN_TO_ADDRESS=lower('0xe22D2beDb3Eca35E6397e0C6D62857094aA26F52') AND TX_STATUS='SUCCESS' ), USDT_Data_outflow_arbi AS( SELECT DATE_TRUNC('day', BLOCK_TIMESTAMP) as date, ORIGIN_FROM_ADDRESS as user_address, EVENT_INPUTS:tokensBought/pow(10, 6) as amount_usd FROM arbitrum.CORE.FACT_EVENT_LOGS WHERE EVENT_NAME='TokenSwap' AND ORIGIN_TO_ADDRESS=lower('0xCB0a4177E0A60247C0ad18Be87f8eDfF6DD30283') AND TX_STATUS='SUCCESS' ), DAI_Data_outflow_arbi AS( SELECT DATE_TRUNC('day', BLOCK_TIMESTAMP) as date, ORIGIN_FROM_ADDRESS as user_address, EVENT_INPUTS:tokensBought/pow(10, 18) as amount_usd FROM arbitrum.CORE.FACT_EVENT_LOGS WHERE EVENT_NAME='TokenSwap' AND ORIGIN_TO_ADDRESS=lower('0xe7F40BF16AB09f4a6906Ac2CAA4094aD2dA48Cc2') AND TX_STATUS='SUCCESS' ), ETH_DATA_TEMP_outflow_arbi AS( SELECT DATE_TRUNC('day', BLOCK_TIMESTAMP) as date,ORIGIN_FROM_ADDRESS as user_address, EVENT_INPUTS:tokensBought/pow(10, 18) as amount_ETH FROM arbitrum.CORE.FACT_EVENT_LOGS WHERE EVENT_NAME='TokenSwap' AND ORIGIN_TO_ADDRESS=lower('0x33ceb27b39d2Bb7D2e61F7564d3Df29344020417') AND TX_STATUS='SUCCESS' ), ETH_DATA_WITH_PRICE_outflow_arbi AS( SELECT ETH_DATA_TEMP_outflow_arbi.*, ETH_DAILY_PRICE.PRICE AS PRICE FROM ETH_DATA_TEMP_outflow_arbi INNER JOIN ETH_DAILY_PRICE ON ETH_DATA_TEMP_outflow_arbi.date = ETH_DAILY_PRICE.date ), ETH_DATA_outflow_arbi AS( SELECT date, user_address, amount_ETH*PRICE as amount_usd FROM ETH_DATA_WITH_PRICE_outflow_arbi ), WBTC_DATA_TEMP_outflow_arbi AS( SELECT DATE_TRUNC('day', BLOCK_TIMESTAMP) as date,ORIGIN_FROM_ADDRESS as user_address, EVENT_INPUTS:tokensBought/pow(10, 8) as amount_WBTC FROM arbitrum.CORE.FACT_EVENT_LOGS WHERE EVENT_NAME='TokenSwap' AND ORIGIN_TO_ADDRESS=lower('0xC08055b634D43F2176d721E26A3428D3b7E7DdB5') AND TX_STATUS='SUCCESS' ), WBTC_DATA_WITH_PRICE_outflow_arbi AS( SELECT WBTC_DATA_TEMP_outflow_arbi.*, WBTC_DAILY_PRICE.PRICE AS PRICE FROM WBTC_DATA_TEMP_outflow_arbi INNER JOIN WBTC_DAILY_PRICE ON WBTC_DATA_TEMP_outflow_arbi.date = WBTC_DAILY_PRICE.date ), WBTC_DATA_outflow_arbi AS( SELECT date, user_address, amount_WBTC*PRICE as amount_usd FROM WBTC_DATA_WITH_PRICE_outflow_arbi ), All_TOKEN_DATA_OUTFLOW_arbi AS( SELECT date, amount_usd FROM USDC_DATA_outflow_arbi where date >= '2022-07-01' and date <= '2022-07-31' UNION SELECT date, amount_usd FROM USDT_DATA_outflow_arbi where date >= '2022-07-01' and date <= '2022-07-31' UNION SELECT date, amount_usd FROM DAI_DATA_outflow_arbi where date >= '2022-07-01' and date <= '2022-07-31' UNION SELECT date, amount_usd FROM ETH_DATA_outflow_arbi where date >= '2022-07-01' and date <= '2022-07-31' UNION SELECT date, amount_usd FROM WBTC_DATA_outflow_arbi where date >= '2022-07-01' and date <= '2022-07-31' ), inflow_outflow_date_arbi as( select To_arbitrum.date as date, To_arbitrum.amount_usd as inflow_usd, All_TOKEN_DATA_OUTFLOW_arbi.amount_usd as outflow_usd from To_arbitrum inner join All_TOKEN_DATA_OUTFLOW_arbi on To_arbitrum.date = All_TOKEN_DATA_OUTFLOW_arbi.date ) SELECT date, sum(inflow_usd) as volume_inflow, SUM(volume_inflow) OVER(ORDER BY DATE ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Cumulative_Volume_inflow, sum(outflow_usd) as volume_outflow, sum(volume_outflow) OVER(ORDER BY DATE ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Cumulative_Volume_outflow, 'optimism' as "blockchain" FROM inflow_outflow_date GROUP BY DATE union SELECT date, sum(inflow_usd) as volume_inflow, SUM(volume_inflow) OVER(ORDER BY DATE ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Cumulative_Volume_inflow, sum(outflow_usd) as volume_outflow, sum(volume_outflow) OVER(ORDER BY DATE ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Cumulative_Volume_outflow, 'arbitrum' as "blockchain" FROM inflow_outflow_date_arbi GROUP BY DATE

Open Analytics Bounty: Arbitrum Vs. Optimism in July