Query editor
with ETH_REVENUE as (WITHETH_PRICES AS (select contract_address as token,avg(price) as price,max(decimals) as decimalsFROMprices.usdWHERE(blockchain = 'ethereum')andsymbol IN ('WETH', 'APE', 'CVX', 'CRV', 'USDC', 'wstETH', 'BAL', 'AURA', 'LOOKS')andday(CURRENT_TIMESTAMP - minute) <= 2group by 1),SY_DEPOSITS AS(select block_time, contract_address as sy, bytearray_substring(topic3, 13, 20) as tokenIn, bytearray_to_uint256(bytearray_substring(data, 1, 32)) as amountToken, bytearray_to_uint256(bytearray_substring(data, 1+32, 32)) as amountSYfrom ethereum.logswhere contract_address in(0x47BA20283Be4d72D4AFB1862994F4203551539C5,0xc0CA1e345bE44Ea2c83202222E5084ae3a01e9DC,0xdf7083f2A0F8a191ab5eEAFebE92ED21cD3Dd915,0x8267fdabd1b8C8645138f2dE5B0fe24988DC9820,0xD393D1dDd6B8811A86D925F5E14014282581bC04,0x35C16314D6Ee4753289E5cC15A5C5E1Dd4eaD345,0xcbC72d92b2dc8187414F6734718563898740C0BC)and topic0 = 0x5fe47ed6d4225326d3303476197d782ded5a4e9c14f479dc9ec4992af4e85d59order by block_time desc),SY_RAW_RATES as (select sy, amountSY, cast(amountToken as double) / pow(10,decimals) * price as amountUSD from SY_DEPOSITS sd join ETH_PRICES ep on sd.tokenIn = ep.token),SY_FINAL_RATES as (select sy as token, cast(amountSY as double) as amountToken, amountUSD from (select sy, amountSY, amountUSD, row_number() over (partition by sy) as row_order from SY_RAW_RATES) temp where row_order = 1),ALL_TOKEN_PRICES as (select * from (select * from SY_FINAL_RATESunionselect token, pow(10,decimals) as amountToken, price as amountUSD from ETH_PRICES)),TRANSFER_TO_TREASURY as (select tx_hash, contract_address as token, date_trunc('day', block_time) as time, bytearray_to_decimal(bytearray_substring(data, 17, 16)) as amountfrom ethereum.logswherecontract_address in (select token from ALL_TOKEN_PRICES)and topic0 = 0xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef and topic2 = 0x0000000000000000000000008270400d528c34e1596ef367eedec99080a1b592)-- SELECT-- tx_hash,t.amount / p.amountToken * p.amountUSD as value, t.time as time-- FROM-- TRANSFER_TO_TREASURY t-- join