Query editor
WITH volumes AS (SELECT block_time,CAST(varbinary_to_uint256(varbinary_substring(data, 193, 32)) AS DOUBLE) / 1e18 AS eth_amountFROM robinhood.logsWHERE contract_address = 0x50b9af842dacb8fc7476660cc86d3787cad66ac4AND topic0 = 0xe2d4c4d950c33268021bcd67d1421807db799277a75376d17d5e2787849180a4UNION ALLSELECT block_time,ABS(CAST(varbinary_to_int256(varbinary_substring(data, 97, 32)) AS DOUBLE)) / 1e18 AS eth_amountFROM robinhood.logsWHERE contract_address = 0x50b9af842dacb8fc7476660cc86d3787cad66ac4AND topic0 = 0x98f251f649dccf76b2cfd5230f0405fbcc0251e821b4eb4c07ff31522076c0d9), eth_price AS (SELECT DATE_TRUNC('day', timestamp) AS day, AVG(price) AS eth_usdFROM prices.dayWHERE blockchain = 'robinhood'AND contract_address = 0x0bd7d308f8e1639fab988df18a8011f41eacad73GROUP BY 1)SELECT DATE_TRUNC('day', v.block_time) AS day,'InkyPump' AS symbol,SUM(v.eth_amount * p.eth_usd) AS volume_usdFROM volumes vLEFT JOIN eth_price p ON p.day = DATE_TRUNC('day', v.block_time)WHERE DATE_TRUNC('day', v.block_time) < DATE_TRUNC('day', NOW())GROUP BY 1, 2ORDER BY 1