--credited to @rohollah 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-11-06' and date ='2022-11-06' and date ='2022-11-06' and date ='2022-11-06' and date ='2022-11-06' and date ='2022-11-06' and date ='2022-11-06' and date ='2022-11-06' and date ='2022-11-06' and date ='2022-11-06' and date ='2022-11-06' and date ='2022-11-06' and date ='2022-11-06' and date ='2022-11-06' and date

Open Analytics Bounty: Optimism (November 12)-FTX’ insolvency