Query editor
-- Sonic, mes x par, con el mismo esquema que query 8218596 para que entre en el pipeline local.-- gate_class = router_total (en sonic nadie llama directo al router; ver findings §3).with s as (select date_trunc('month', evt_block_time) as m, cast(evt_block_time as date) as d, 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, decimals, symbolfrom prices.day where blockchain = 'sonic' and timestamp >= timestamp '2025-07-01'), anchors (tok) as (values(0x039e2fb66102314ce7b64ce5ce3e5183bc94ad38) -- wS, (0x29219dd400f2bf60e5a23d13be72b486d4038894) -- USDC.e, (0x50c42deacd8fc9773493ed674b675be577f2634b) -- WETH, (0xd3dce716f3ef535c5ff8d041c1a41c3bd89b97ae) -- scUSD, (0x6047828dc181963ba44974801ff68e538da5eaf9) -- USDT, (0xe5da20f15420ad15de0fa650600afc998bbe3955) -- stS), c as (select s.m, s.token_in, s.token_out, pin.symbol as sym_in, pout.symbol as sym_out, 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), g as (select m, token_in, token_out, max(sym_in) as sym_in, max(sym_out) as sym_out, count_if(in_usd > 0 and out_usd is not null and abs(out_usd / in_usd - 1) <= 0.10) as swaps_clean, coalesce(sum(case when in_usd > 0 and out_usd is not null and abs(out_usd / in_usd - 1) <= 0.10 then in_usd end), 0) as vol_in_usd_clean, coalesce(sum(case when in_usd > 0 and out_usd is not null and abs(out_usd / in_usd - 1) <= 0.10 then out_usd end), 0) as vol_out_usd_cleanfrom c group by 1,2,3)select 'sonic' as chain, cast(g.m as date) as month, to_hex(g.token_in) as token_in, to_hex(g.token_out) as token_out, g.sym_in, g.sym_out, g.swaps_clean, g.vol_in_usd_clean, g.vol_out_usd_clean, (ain.tok is not null or aout.tok is not null) as anchoredfrom gleft join anchors ain on ain.tok = g.token_inleft join anchors aout on aout.tok = g.token_outwhere g.swaps_clean > 0