Query editor
-- Sonic: el router tiene movimiento pero NADIE lo llama directo (no existe el gate 0x3d90f66b),-- asi que el discriminador de flujo TW da cero. Se mide el ROUTER COMPLETO y se etiqueta como tal.with s as (select cast(evt_block_time as date) as d, date_trunc('month', evt_block_time) as m, evt_tx_to as gate, sender, case when fromToken = 0xeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeethen 0x039e2fb66102314ce7b64ce5ce3e5183bc94ad38 else fromToken end as token_in, case when toToken = 0xeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeethen 0x039e2fb66102314ce7b64ce5ce3e5183bc94ad38 else toToken end as token_out, fromAmount as in_raw, returnAmount as out_rawfrom liquidmesh_sonic.liquidmeshrouter_evt_orderrecord), p as (select contract_address, cast(timestamp as date) as d, price, decimalsfrom prices.day where blockchain = 'sonic' and timestamp >= timestamp '2025-07-01'), c as (select s.m, s.d >= date '2026-01-01' and s.d <= date '2026-07-29' as in_xo, s.sender, cast(s.in_raw as double) / power(10, pin.decimals) * pin.price as in_usd, cast(s.out_raw as double) / power(10, pout.decimals) * pout.price as out_usdfrom sleft join p pin on pin.contract_address = s.token_in and pin.d = s.dleft join p pout on pout.contract_address = s.token_out and pout.d = s.d), f as (select m, in_xo, sender, in_usd, out_usd, in_usd is not null and out_usd is not null as priced, in_usd > 0 and out_usd is not null and abs(out_usd / in_usd - 1) <= 0.10 as cleanfrom c)select 'sonic' as chain, 'router_total' as gate_class, case when grouping(m) = 0 then 'month'when grouping(in_xo) = 0 then (case when in_xo then 'xo_window' else 'outside_xo' end)else 'total' end as grain, m as month, count(*) as swaps_total, count_if(priced) as swaps_priced, count_if(clean) as swaps_clean, count(distinct sender) as senders_total, count(distinct case when clean then sender end) as senders_clean, coalesce(sum(case when priced then in_usd end), 0) as vol_in_usd_priced, coalesce(sum(case when clean then in_usd end), 0) as vol_in_usd_clean, coalesce(sum(case when clean then out_usd end), 0) as vol_out_usd_clean, approx_percentile(case when clean then in_usd end, 0.5) as median_ticket_usd, approx_percentile(case when clean then in_usd end, 0.9) as p90_ticket_usd, approx_percentile(case when clean then 1 - out_usd / in_usd end, 0.5) as median_all_in_costfrom fgroup by grouping sets ((m), (in_xo), ())