Query editor
-- Ekubo ve(3,3) "STONX" on Robinhood Chain — headline KPIs-- Ve33 extension 0xd18685a514e59b06d59824e16db07e73345d9953 | Core 0x00000000000014aa86c5d3c41765bb24e11bd701WITH 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),lg AS (SELECT data, length(data) AS len, block_time, tx_fromFROM robinhood.logsWHERE contract_address = 0x00000000000014aa86c5d3c41765bb24e11bd701AND length(data) IN (116, 192)AND block_date >= DATE '2026-07-29'),dec AS (SELECTCASE WHEN len = 116 THEN substr(data, 21, 32) ELSE substr(data, 33, 32) END AS pool_id,len, block_time, tx_from,CASE WHEN len = 116 THEN substr(data, 53, 16) ELSE substr(data, 129, 16) END AS d0b,CASE WHEN len = 116 THEN substr(data, 69, 16) ELSE substr(data, 145, 16) END AS d1bFROM lg),num AS (SELECT pool_id, len, block_time, tx_from,cast(from_big_endian_64(substr(d0b, 1, 8)) AS double) * 18446744073709551616.0+ (CASE WHEN from_big_endian_64(substr(d0b, 9, 8)) >= 0 THEN cast(from_big_endian_64(substr(d0b, 9, 8)) AS double)ELSE cast(from_big_endian_64(substr(d0b, 9, 8)) AS double) + 18446744073709551616.0 END) AS delta0,cast(from_big_endian_64(substr(d1b, 1, 8)) AS double) * 18446744073709551616.0+ (CASE WHEN from_big_endian_64(substr(d1b, 9, 8)) >= 0 THEN cast(from_big_endian_64(substr(d1b, 9, 8)) AS double)ELSE cast(from_big_endian_64(substr(d1b, 9, 8)) AS double) + 18446744073709551616.0 END) AS delta1FROM dec),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),-- cumulative swap volume, valued off the USDG / ETH leg of each pairvol AS (SELECTcount(*) AS swaps,count(DISTINCT n.tx_from) AS unique_traders,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)