Query editor
WITH tok AS (SELECT contract_address, symbol, decimals FROM tokens.erc20 WHERE blockchain = 'robinhood'),keys AS (SELECT DISTINCTfrom_hex(substr(json_extract_scalar(poolKey, '$.token0'), 3)) AS token0,from_hex(substr(json_extract_scalar(poolKey, '$.token1'), 3)) AS token1,from_hex(substr(json_extract_scalar(poolKey, '$.config'), 3)) AS configFROM stonx_robinhood.ve33_call_beforeinitializepool),pools AS (SELECT keccak(concat(0x000000000000000000000000, token0, 0x000000000000000000000000, token1, config)) AS pool_id,token0, token1, coalesce(t0.decimals, 18) AS dec0, coalesce(t1.decimals, 18) AS dec1FROM keys kLEFT JOIN tok t0 ON t0.contract_address = k.token0LEFT JOIN tok t1 ON t1.contract_address = k.token1),px AS (SELECT contract_address, date_trunc('hour', timestamp) AS h, avg(price) AS priceFROM prices.hour WHERE blockchain = 'robinhood' AND timestamp >= TIMESTAMP '2026-07-30' GROUP BY 1,2),-- per-token fallback: that token's own earliest known price (never another token's)tokpx AS (SELECT contract_address, min_by(price, timestamp) AS first_priceFROM prices.hour WHERE blockchain = 'robinhood' AND timestamp >= TIMESTAMP '2026-07-30' GROUP BY 1),-- native ETH (0x0) has no price row of its own; use WETH on the same chainethp AS (SELECT date_trunc('hour', timestamp) AS h, avg(price) AS priceFROM prices.hour WHERE blockchain = 'robinhood'AND contract_address = 0x0bd7d308f8e1639fab988df18a8011f41eacad73AND timestamp >= TIMESTAMP '2026-07-30' GROUP BY 1),f AS (SELECTe.evt_block_date AS day,cast(e.amount0 AS double) / power(10, p.dec0)* (CASE WHEN p.token0 = 0x0000000000000000000000000000000000000000 THEN ep.priceELSE coalesce(px0.price, tp0.first_price) END) AS usd0,cast(e.amount1 AS double) / power(10, p.dec1)* (CASE WHEN p.token1 = 0x0000000000000000000000000000000000000000 THEN ep.priceELSE coalesce(px1.price, tp1.first_price) END) AS usd1FROM stonx_robinhood.ve33_evt_poolfeesaccounted eJOIN pools p ON p.pool_id = e.poolIdLEFT JOIN px px0 ON px0.contract_address = p.token0 AND px0.h = date_trunc('hour', e.evt_block_time)LEFT JOIN px px1 ON px1.contract_address = p.token1 AND px1.h = date_trunc('hour', e.evt_block_time)LEFT JOIN tokpx tp0 ON tp0.contract_address = p.token0LEFT JOIN tokpx tp1 ON tp1.contract_address = p.token1LEFT JOIN ethp ep ON ep.h = date_trunc('hour', e.evt_block_time))SELECTday,sum(coalesce(usd0, 0) + coalesce(usd1, 0)) AS fees_usd,sum(sum(coalesce(usd0, 0) + coalesce(usd1, 0))) OVER (ORDER BY day) AS cumulative_fees_usdFROM fGROUP BY 1ORDER BY 1