Query editor
with metamask_attribution as (select tx_hash,casewhen topic1 = 0xf35f348d53012d52a5d39f9390d246956ac932d5778d2bb49e359dba4fa0896d then '1inch Router'when topic1 = 0x62db2e87ad4360c521a990935f97f7c44833c4f99a8f1c09129331715b07f525 then 'SCP'when topic1 = 0x0f15df26153967931fbcb8c0d3b573946eced4ef196e2d07ec13f437ff36584e then 'Airswap Router'when topic1 = 0x015123c6e2552626efe611b6c48de60d080a6650860a38f237bc2b6f651f79d1 then 'Airswap Router'when topic1 = 0x9e4182a38ef65fd4828a1563124c6ada8e2cd461a1a638cbc46803f0cd3e7f48 then 'Paraswap Router'when topic1 = 0xcbf46790f422d91c0bc3f78784cac5d123211479f745ef4d632dfd71fd4d53f3 then 'Openocean Router'when topic1 = 0xa8dc30b66c6d4a8aac3d15925bfca09e42cac4a00c50f9949154b045088e2ac2 then '0x API Router'when topic1 = 0x3ef3c00071253ec2c927305151c4ef63c615047982b99857a44557bf0c1e3c45 then 'Hashflow'when topic1 = 0xbd5c436f8c83379009c1962310b8347e561d1900906d3fe4075b1596f8955f88 then '1inch Router'when topic1 = 0x7fc98a4c80dacc6cf251292e4c6990ddd6eb1047c516736ec574def98cef0865 then 'Bebop Router'when topic1 = 0xa06d1ebec95550faee235885bf6dd56b011479008896fbe07e1c3bf19fead10e then 'Kyber Router'when topic1 = 0x69b1f17ff273de3fb47eab72186e121a076c1b07d7ea487a85664e14fbcc9e75 then '1inch Router'else cast(topic1 as varchar)end as aggregatorfrom ethereum.logswherecontract_address = 0x881d40237659c251811cec9c364ef91dc08d300cgroup by 1, 2), agg_base_tx as (select distinct t.tx_hash, project as aggfrom dex_aggregator.trades twhere t.blockchain = 'ethereum'and project not in ('CoW Protocol','bebop', '1inch Limit Order Protocol')), zeroex as (select distinct l.tx_hash,case when f.affiliate_address= 0x382ffce2287252f930e1c8dc9328dac5bf282ba1 then 'Coinbase Wallet'when f.affiliate_address in (0x86003b044f70dac0abc80ac8957305b6370893ed, 0x9b858be6e3047d88820f439b240deac2418a2551) then 'Matcha'when f.affiliate_address= 0x08a3c2a819e3de7aca384c798269b3ce1cd0e437 then 'DefiLlama (Filled by 0x API ONLY)'when f.affiliate_address is not null then '0x API Integrators' else null end as affiliate-- from query_3151907 lfrom dune.flashbots.result_liquidity_version_alltime_dex_base_table ljoin zeroex.api_fills f on f.tx_hash = l.tx_hash)selectt.tx_hash as hash,t.block_time,b.number as block_number,t.tx_from as user,case when m.tx_hash is not null then 'Maestro Telegram Bot'when routers.frontend = '0x API Integrators' then coalesce(zeroex.affiliate, '0x API Integrators')when routers.router ='Integral Relayer' then( case when z.agg LIKE '%1inch%' then '1inch Integrators'when z.agg = 'DODO' then 'DODO Integrators'when z.agg = 'paraswap' then 'Paraswap Integrators'when z.agg = '0x API' then '0x API Integrators'else z.agg end ) -- when it's integral relayer, frontend is aggregatorelse routers.frontend end as frontend,case when routers.frontend = '0x API Integrators' and zeroex.affiliate like '%DefiLlama%' then 'DefiLlama'else metaagg_routers.router end as metaaggregator,case when t.tx_to = 0x881d40237659c251811cec9c364ef91dc08d300c then mma.aggregatorwhen z.agg LIKE '%1inch%' then '1inch Router'when z.agg = 'DODO' then 'DODO Router'when z.agg = 'paraswap' then 'Paraswap Router'when z.agg = '0x API' then '0x API Router'else routers.router end as solver,tt.amount_usd as trade_usd,