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.symbol, 'ETH') AS sym0, coalesce(t1.symbol, 'ETH') AS sym1,coalesce(t0.decimals, 18) AS dec0, coalesce(t1.decimals, 18) AS dec1,from_big_endian_32(substr(config, 29, 4)) AS type_configFROM keys kLEFT JOIN tok t0 ON t0.contract_address = k.token0LEFT JOIN tok t1 ON t1.contract_address = k.token1),lg AS (SELECT data, length(data) AS len, block_timeFROM robinhood.logsWHERE contract_address = 0x00000000000014aa86c5d3c41765bb24e11bd701AND length(data) IN (116, 192) AND block_date >= DATE '2026-07-29'),num AS (SELECTCASE WHEN len = 116 THEN substr(data, 21, 32) ELSE substr(data, 33, 32) END AS pool_id,len, block_time,cast(from_big_endian_64(substr(CASE WHEN len = 116 THEN substr(data, 53, 16) ELSE substr(data, 129, 16) END, 1, 8)) AS double) * 18446744073709551616.0+ (CASE WHEN from_big_endian_64(substr(CASE WHEN len = 116 THEN substr(data, 53, 16) ELSE substr(data, 129, 16) END, 9, 8)) >= 0THEN cast(from_big_endian_64(substr(CASE WHEN len = 116 THEN substr(data, 53, 16) ELSE substr(data, 129, 16) END, 9, 8)) AS double)ELSE cast(from_big_endian_64(substr(CASE WHEN len = 116 THEN substr(data, 53, 16) ELSE substr(data, 129, 16) END, 9, 8)) AS double) + 18446744073709551616.0 END) AS delta0,cast(from_big_endian_64(substr(CASE WHEN len = 116 THEN substr(data, 69, 16) ELSE substr(data, 145, 16) END, 1, 8)) AS double) * 18446744073709551616.0+ (CASE WHEN from_big_endian_64(substr(CASE WHEN len = 116 THEN substr(data, 69, 16) ELSE substr(data, 145, 16) END, 9, 8)) >= 0THEN cast(from_big_endian_64(substr(CASE WHEN len = 116 THEN substr(data, 69, 16) ELSE substr(data, 145, 16) END, 9, 8)) AS double)ELSE cast(from_big_endian_64(substr(CASE WHEN len = 116 THEN substr(data, 69, 16) ELSE substr(data, 145, 16) END, 9, 8)) AS double) + 18446744073709551616.0 END) AS delta1FROM lg),pxh AS (SELECT contract_address, date_trunc('hour', timestamp) AS h, avg(price) AS priceFROM prices.hour WHERE blockchain = 'robinhood' AND timestamp >= TIMESTAMP '2026-07-29' GROUP BY 1,2),pxl AS (SELECT contract_address, max_by(price, timestamp) AS priceFROM prices.hour WHERE blockchain = 'robinhood' AND timestamp >= TIMESTAMP '2026-07-29' GROUP BY 1),eth_h AS (SELECT h, price FROM pxh WHERE contract_address = 0x0bd7d308f8e1639fab988df18a8011f41eacad73),eth_l AS (SELECT price FROM pxl WHERE contract_address = 0x0bd7d308f8e1639fab988df18a8011f41eacad73),vol AS (SELECT n.pool_id, count(*) AS swaps,sum(CASEWHEN p.token0 = 0x5fc5360d0400a0fd4f2af552add042d716f1d168 THEN abs(n.delta0) / power(10, p.dec0) * coalesce(px0.price, 1.0)WHEN p.token1 = 0x5fc5360d0400a0fd4f2af552add042d716f1d168 THEN abs(n.delta1) / power(10, p.dec1) * coalesce(px1.price, 1.0)WHEN p.token0 = 0x0000000000000000000000000000000000000000 THEN abs(n.delta0) / 1e18 * ep.priceWHEN p.token1 = 0x0000000000000000000000000000000000000000 THEN abs(n.delta1) / 1e18 * ep.priceWHEN p.token0 = 0x0bd7d308f8e1639fab988df18a8011f41eacad73 THEN abs(n.delta0) / 1e18 * coalesce(px0.price, ep.price)WHEN p.token1 = 0x0bd7d308f8e1639fab988df18a8011f41eacad73 THEN abs(n.delta1) / 1e18 * coalesce(px1.price, ep.price)ELSE abs(n.delta0) / power(10, p.dec0) * px0.price END) AS volume_usdFROM num n JOIN pools p ON p.pool_id = n.pool_idLEFT JOIN pxh px0 ON px0.contract_address = p.token0 AND px0.h = date_trunc('hour', n.block_time)LEFT JOIN pxh px1 ON px1.contract_address = p.token1 AND px1.h = date_trunc('hour', n.block_time)LEFT JOIN eth_h ep ON ep.h = date_trunc('hour', n.block_time)WHERE n.len = 116GROUP BY 1