Query editor
WITH tk AS (SELECT * FROM (VALUES(0x55d398326f99059ff775485246999027b3197955, 'USDT', 18),(0x8ac76a51cc950d9822d68b83fe1ad97b32cd580d, 'USDC', 18),(0xbb4cdb9cbd36b01bd1cbaebf2de08d9173bc095c, 'WBNB', 18)) AS t(addr, sym, dec)),px AS (SELECT contract_address, timestamp AS d, price FROM prices.dayWHERE blockchain = 'bnb' AND timestamp >= TIMESTAMP '2026-01-01' AND timestamp < TIMESTAMP '2026-07-30'AND contract_address IN (SELECT addr FROM tk)),bnbpx AS (SELECT d, price FROM px WHERE contract_address = 0xbb4cdb9cbd36b01bd1cbaebf2de08d9173bc095c),po AS (SELECT t."to" AS usuario, t.evt_block_time AS ts, tk.sym AS out_sym,t.value / power(10, tk.dec) * px.price AS out_usdFROM erc20_bnb.evt_Transfer tJOIN tk ON tk.addr = t.contract_addressJOIN px ON px.contract_address = t.contract_address AND px.d = date_trunc('day', t.evt_block_time)WHERE t.evt_block_time >= TIMESTAMP '2026-01-01' AND t.evt_block_time < TIMESTAMP '2026-07-30'AND t."from" = 0x51dbd97fa14f4d78f8afc2e92a692a9e087efb5cUNION ALLSELECT x."to", x.block_time, 'BNB', CAST(x.value AS double) / 1e18 * b.priceFROM bnb.transactions x JOIN bnbpx b ON b.d = date_trunc('day', x.block_time)WHERE x.block_time >= TIMESTAMP '2026-01-01' AND x.block_time < TIMESTAMP '2026-07-30'AND x.value > CAST(0 AS uint256) AND x."from" = 0x51dbd97fa14f4d78f8afc2e92a692a9e087efb5c),pou AS (SELECT * FROM po WHERE out_usd BETWEEN 10 AND 50000),usr AS (SELECT DISTINCT usuario FROM pou),dep AS (SELECT t."from" AS usuario, t.evt_block_time AS ts, tk.sym AS in_sym,t.value / power(10, tk.dec) * px.price AS in_usdFROM erc20_bnb.evt_Transfer tJOIN usr u ON u.usuario = t."from"JOIN tk ON tk.addr = t.contract_addressJOIN px ON px.contract_address = t.contract_address AND px.d = date_trunc('day', t.evt_block_time)WHERE t.evt_block_time >= TIMESTAMP '2026-01-01' AND t.evt_block_time < TIMESTAMP '2026-07-30'UNION ALLSELECT x."from", x.block_time, 'BNB', CAST(x.value AS double) / 1e18 * b.priceFROM bnb.transactions x JOIN usr u ON u.usuario = x."from"JOIN bnbpx b ON b.d = date_trunc('day', x.block_time)WHERE x.block_time >= TIMESTAMP '2026-01-01' AND x.block_time < TIMESTAMP '2026-07-30'AND x.value > CAST(0 AS uint256)),depu AS (SELECT * FROM dep WHERE in_usd BETWEEN 10 AND 50000),m AS (SELECT depu.in_sym, pou.out_sym, depu.in_usd, pou.usuario,pou.out_usd / depu.in_usd AS ratio,date_diff('second', depu.ts, pou.ts) AS settle_s,row_number() OVER (PARTITION BY pou.usuario, pou.ts ORDER BY abs(pou.out_usd / depu.in_usd - 0.978)) AS rn_po,row_number() OVER (PARTITION BY depu.usuario, depu.ts ORDER BY abs(pou.out_usd / depu.in_usd - 0.978)) AS rn_depFROM pou JOIN depu ON depu.usuario = pou.usuarioAND depu.ts < pou.ts AND depu.ts > pou.ts - INTERVAL '30' MINUTEWHERE pou.out_usd / depu.in_usd BETWEEN 0.95 AND 1.01 AND depu.in_sym <> pou.out_sym)SELECT in_sym, out_sym, count(*) AS swaps, count(DISTINCT usuario) AS usuarios,round(sum(in_usd), 0) AS usd_input,round(approx_percentile(in_usd, 0.5), 0) AS ticket_mediano,round((1 - approx_percentile(ratio, 0.5)) * 100, 2) AS costo_p50_pct,round(approx_percentile(settle_s, 0.5), 0) AS settle_p50_s