Query editor
WITH a AS (SELECT * FROM (VALUES(0x5b8d2b4acbc8021f38f63ae67b6dd24b4dfca066, 'P-A', 'payIn estatica'),(0x51dbd97fa14f4d78f8afc2e92a692a9e087efb5c, 'P-A', 'pagador'),(0xba4e328c6f9a5356db1cf17a04062f4d6d6e5153, 'exodus:int2', 'hub deposito'),(0xfb19ffd1ff9316b7f5bba076ef4b78e4bbedf4e1, 'exodus:int2', 'pagador'),(0x016606acc6b0cfe537acc221e3bf1bb44b4049ee, 'P-B', 'pagador+hub'),(0xdd3d72c53ff982ff59853da71158bf1538b3ceee, 'P-C', 'pagador+hub')) AS t(addr, prov, rol)),tk AS (SELECT * FROM (VALUES(0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48, 6), (0xdac17f958d2ee523a2206206994597c13d831ec7, 6),(0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2, 18), (0x6b175474e89094c44da98b954eedeac495271d0f, 18),(0x6c3ea9036406852006290770bedfcaba0e23a0e8, 6), (0x2260fac5e5542a773aa44fbcfedf7c193bc2c599, 8),(0x45804880de22913dafe09f4980848ece6ecbaf78, 18)) AS t(addr, dec)),pr AS (SELECT contract_address, avg(price) AS price FROM prices.dayWHERE blockchain = 'ethereum' AND timestamp >= TIMESTAMP '2026-01-01' AND timestamp < TIMESTAMP '2026-07-30'GROUP BY 1),eth_px AS (SELECT price FROM pr WHERE contract_address = 0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2),f AS (SELECT t."to" AS anchor, t."from" AS cp, 'IN' AS dir, 'erc20' AS clase,t.value / power(10, tk.dec) * pr.price AS usdFROM erc20_ethereum.evt_Transfer t JOIN tk ON tk.addr = t.contract_addressJOIN pr ON pr.contract_address = t.contract_addressWHERE t.evt_block_time >= TIMESTAMP '2026-01-01' AND t.evt_block_time < TIMESTAMP '2026-07-30'AND t."to" IN (SELECT addr FROM a)UNION ALLSELECT t."from", t."to", 'OUT', 'erc20',t.value / power(10, tk.dec) * pr.priceFROM erc20_ethereum.evt_Transfer t JOIN tk ON tk.addr = t.contract_addressJOIN pr ON pr.contract_address = t.contract_addressWHERE t.evt_block_time >= TIMESTAMP '2026-01-01' AND t.evt_block_time < TIMESTAMP '2026-07-30'AND t."from" IN (SELECT addr FROM a)UNION ALLSELECT x."to", x."from", 'IN', 'nativo',CAST(x.value AS double) / 1e18 * (SELECT price FROM eth_px)FROM ethereum.transactions xWHERE x.block_time >= TIMESTAMP '2026-01-01' AND x.block_time < TIMESTAMP '2026-07-30'AND x.value > CAST(0 AS uint256) AND x."to" IN (SELECT addr FROM a)UNION ALLSELECT x."from", x."to", 'OUT', 'nativo',CAST(x.value AS double) / 1e18 * (SELECT price FROM eth_px)FROM ethereum.transactions xWHERE 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" IN (SELECT addr FROM a))SELECT a.prov, a.rol, f.dir, f.clase,count(*) AS movimientos, count(DISTINCT f.cp) AS contrapartes,round(sum(f.usd) / 1e6, 2) AS usd_millones,round(approx_percentile(f.usd, 0.5), 0) AS usd_medianaFROM f JOIN a ON a.addr = f.anchorGROUP BY 1, 2, 3, 4 ORDER BY 1, 2, 3, 4