select block_timestamp, tx_hash, contract_address, 'yes' as aggregator

from arbitrum.core.fact_event_logs

where contract_address in ('0x1111111254fb6c44bac0bed2854e76f90643097d', '0x6352a56caadc4f1e25cd6c75970fa768a3304e64',

'0xe8c97bf6d084880de38aec1a56d97ed9fdfa0c9b','0xdef171fe48cf0115b1d80b88dc8eab59176fee57','0xdef1c0ded9bec7f1a1670819833240f027b25eff')

),

swaps as (

select *

from arbitrum.core.fact_event_logs

where event_name='Swap' and contract_address=lower('0x0c1cf6883efa1b496b01f654e247b9b419873054')

),

combine as (

select s.tx_hash, s.block_timestamp, origin_from_address, origin_to_address, s.contract_address as swap_contract, t.contract_address as router_address, event_name,aggregator,

(event_inputs:amount1In*pow(10,-18) +event_inputs:amount1Out*pow(10,-18) )/2 as swap_vol

from tb1 t

full outer join swaps s on t.tx_hash=s.tx_hash

where event_name='Swap' and CURRENT_DATE - date(s.block_timestamp) <=90

)

select sum(swap_vol) as vol, count(tx_hash) as tx_count, aggregator, router_address,

case when router_address='0x1111111254fb6c44bac0bed2854e76f90643097d' then '1inch'

when router_address='0x6352a56caadc4f1e25cd6c75970fa768a3304e64' then 'OpenOcean'

when router_address='0xe8c97bf6d084880de38aec1a56d97ed9fdfa0c9b' then 'SlingShot'

when router_address='0xdef171fe48cf0115b1d80b88dc8eab59176fee57' then 'ParaSwap'

when router_address='0xdef1c0ded9bec7f1a1670819833240f027b25eff' then 'Matcha'

else 'na' end as address_name

from combine

group by aggregator, router_address,address_name