Query editor
WITH payer AS (SELECT * FROM (VALUES(0x51dbd97fa14f4d78f8afc2e92a692a9e087efb5c, 'P-A'),(0xfb19ffd1ff9316b7f5bba076ef4b78e4bbedf4e1, 'sin-atribuir'),(0x016606acc6b0cfe537acc221e3bf1bb44b4049ee, 'P-B'),(0xdd3d72c53ff982ff59853da71158bf1538b3ceee, 'P-hex14')) AS t(addr, prov)),tk AS (SELECT * FROM (VALUES(0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48, 'USDC', 6),(0xdac17f958d2ee523a2206206994597c13d831ec7, 'USDT', 6),(0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2, 'WETH', 18),(0x6b175474e89094c44da98b954eedeac495271d0f, 'DAI', 18),(0x6c3ea9036406852006290770bedfcaba0e23a0e8, 'PYUSD', 6),(0x45804880de22913dafe09f4980848ece6ecbaf78, 'PAXG', 18)) AS t(addr, sym, dec)),px AS (SELECT contract_address, timestamp AS d, price FROM prices.dayWHERE blockchain = 'ethereum' AND timestamp >= TIMESTAMP '2026-01-01' AND timestamp < TIMESTAMP '2026-07-30'AND contract_address IN (SELECT addr FROM tk)),mv AS (SELECT t.evt_block_time AS ts, t."from" AS frm, t."to" AS dst, tk.sym,t.value / power(10, tk.dec) * px.price AS usdFROM erc20_ethereum.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'UNION ALLSELECT x.block_time, x."from", x."to", 'ETH',CAST(x.value AS double) / 1e18 * px.priceFROM ethereum.transactions xJOIN px ON px.contract_address = 0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2AND px.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)),po AS (SELECT p.prov, mv.dst AS usuario, mv.ts, mv.sym AS out_sym, mv.usd AS out_usdFROM mv JOIN payer p ON p.addr = mv.frm WHERE mv.usd BETWEEN 10 AND 50000),dep AS (SELECT mv.frm AS usuario, mv.ts, mv.sym AS in_sym, mv.usd AS in_usdFROM mv WHERE mv.usd BETWEEN 10 AND 50000),m AS (SELECT po.prov, date_trunc('month', po.ts) AS mes, po.usuario,dep.in_sym, po.out_sym, dep.in_usd,po.out_usd / dep.in_usd AS ratio,date_diff('second', dep.ts, po.ts) AS settle_s,row_number() OVER (PARTITION BY po.prov, po.usuario, po.ts ORDER BY abs(po.out_usd / dep.in_usd - 0.978)) AS rn_po,row_number() OVER (PARTITION BY po.prov, dep.usuario, dep.ts ORDER BY abs(po.out_usd / dep.in_usd - 0.978)) AS rn_depFROM po JOIN dep ON dep.usuario = po.usuarioAND dep.ts < po.ts AND dep.ts > po.ts - INTERVAL '30' MINUTEWHERE po.out_usd / dep.in_usd BETWEEN 0.95 AND 1.01 AND dep.in_sym <> po.out_sym)SELECT prov, mes, in_sym, out_sym,count(*) AS swaps, count(DISTINCT usuario) AS usuarios,