Query editor
WITH tk AS (SELECT * FROM (VALUES(0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48, 6),(0xdac17f958d2ee523a2206206994597c13d831ec7, 6),(0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2, 18),(0x6b175474e89094c44da98b954eedeac495271d0f, 18),(0x2260fac5e5542a773aa44fbcfedf7c193bc2c599, 8)) AS t(addr, dec)),pr AS (SELECT contract_address, avg(price) AS price FROM prices.dayWHERE blockchain = 'ethereum' AND timestamp >= TIMESTAMP '2026-07-01' AND timestamp < TIMESTAMP '2026-07-09'AND contract_address IN (SELECT addr FROM tk) GROUP BY 1),mv AS ( -- todos los movimientos de majors de la ventana, valuadosSELECT t.evt_block_time AS ts, t."from" AS frm, t."to" AS dst,t.value / power(10, tk.dec) * pr.price AS usdFROM erc20_ethereum.evt_Transfer tJOIN tk ON tk.addr = t.contract_addressJOIN pr ON pr.contract_address = t.contract_addressWHERE t.evt_block_time >= TIMESTAMP '2026-07-01' AND t.evt_block_time < TIMESTAMP '2026-07-08'),payer AS (SELECT * FROM (VALUES(0x51dbd97fa14f4d78f8afc2e92a692a9e087efb5c, 'P-A'),(0xfb19ffd1ff9316b7f5bba076ef4b78e4bbedf4e1, 'exodus:int2'),(0x016606acc6b0cfe537acc221e3bf1bb44b4049ee, 'P-B'),(0xdd3d72c53ff982ff59853da71158bf1538b3ceee, 'P-C')) AS t(addr, prov)),po AS ( -- payouts a usuarios, banda retailSELECT p.prov, mv.dst AS usuario, mv.ts, mv.usdFROM mv JOIN payer p ON p.addr = mv.frmWHERE mv.usd BETWEEN 10 AND 10000),dep AS ( -- lo que el usuario mandó antes (a cualquier destino)SELECT mv.frm AS usuario, mv.ts, mv.usd FROM mvWHERE mv.usd BETWEEN 10 AND 10000AND mv.dst NOT IN (SELECT addr FROM payer)),m AS (SELECT po.prov, po.usd / dep.usd AS ratio,row_number() OVER (PARTITION BY po.prov, po.usuario, po.tsORDER BY abs(po.usd / dep.usd - 0.978)) AS rnFROM po JOIN dep ON dep.usuario = po.usuarioAND dep.ts < po.ts AND dep.ts > po.ts - INTERVAL '6' HOURWHERE po.usd / dep.usd BETWEEN 0.85 AND 1.06)SELECT prov,floor(ratio * 200) / 200.0 AS bucket_ratio,count(*) AS matches,round(100.0 * count(*) / sum(count(*)) OVER (PARTITION BY prov), 1) AS pct_del_providerFROM m WHERE rn = 1GROUP BY 1, 2HAVING count(*) >= 5ORDER BY prov, bucket_ratio