Query editor
WITH fees AS (SELECT *, DATE_TRUNC('day', block_time) AS blocktime,CASE contract_addressWHEN 0x56549e2fE1D9f0a9556f92494d04F263A5011a00 THEN 'Cryptex Finance: TCAP WBTC Vault Handler'WHEN 0x443366a7a5821619d8d57405511e4fadd9964771 THEN 'Cryptex Finance: TCAP DAI Vault Handler'WHEN 0x717170B66654292dFBd89c39F5aE6753D2aC1381 THEN 'Cryptex Finance: TCAP WETH Vault Handler'WHEN 0xada39d170551daf42822E1D3dA64efEBbD14d1D3 THEN 'Cryptex Finance: TCAP Aave Vault Handler'WHEN 0xbEB44Febc550f69Ff17f8Aa8eeC070B95eF369ba THEN 'Cryptex Finance: TCAP Link Vault Handler'WHEN 0xa8cca36a624215a39d5af6854ac24868559424d3 THEN 'Cryptex Finance: TCAP Hard USDC Vault Handler'WHEN 0xA5b3Bb6e1f206624B3B8CE0c6A0f7614fd35Fa03 THEN 'Cryptex Finance: TCAP Hard DAI Vault Handler'WHEN 0xc2Ba6B8E0EE3cf48B045D966F1dCda767df74833 THEN 'Cryptex Finance: TCAP Hard ETH Vault Handler'WHEN 0x2364536F4891Ed560A6728f4B36871de8176eE5c THEN 'Cryptex Finance: TCAP Hard WBTC Vault Handler'ELSE 'Unknown Address'END AS contract_nameFROM ethereum.logsWHERE contract_address IN (0x56549e2fE1D9f0a9556f92494d04F263A5011a00,0x443366a7a5821619d8d57405511e4fadd9964771,0x717170B66654292dFBd89c39F5aE6753D2aC1381,0xada39d170551daf42822E1D3dA64efEBbD14d1D3,0xbEB44Febc550f69Ff17f8Aa8eeC070B95eF369ba,0xa8cca36a624215a39d5af6854ac24868559424d3,0xA5b3Bb6e1f206624B3B8CE0c6A0f7614fd35Fa03,0xc2Ba6B8E0EE3cf48B045D966F1dCda767df74833,0x2364536F4891Ed560A6728f4B36871de8176eE5c)AND topic0 IN (0xccbea4088a3b7ae9ca2d15fab9a9742a4075b4d7247768a1eecea917565aba00,0xba5789da846390370bbfbf693007be2c95d0cfac69a8d175805f742dc7938321)),price_feeds AS (SELECT DISTINCT DATE_TRUNC('day', minute) AS day, symbol, avg(price) AS priceFROM prices.usdWHERE symbol = 'ETH'AND minute > DATE '2021-04-01'GROUP BY 1,2),final_fees AS (SELECT blocktime AS day, SUM((value * p.price) / 1e18) AS fee_usdFROM fees bLEFT JOIN ethereum.transactions e ON b.tx_hash=e.hashLEFT JOIN price_feeds p ON b.blocktime=p.dayGROUP BY 1)SELECTday,fee_usd,SUM(fee_usd) OVER (ORDER BY day ASC) AS rolling_fee_usdFROM final_feesORDER BY day DESC