with tb1 as (

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

from optimism.core.fact_event_logs

where contract_address in ('0xb7c1cca934641da45f84eda0cf06af73f4a5a523','0x1111111254760f7ab3f16433eea9304126dcd199',lower('0xdef1abe32c034e558cdd535791643c58a13acc10'),

'0x6352a56caadc4f1e25cd6c75970fa768a3304e64','0x00c0184c0b5d42fba6b7ca914b31239b419ab80b',lower('0xdef171fe48cf0115b1d80b88dc8eab59176fee57'))

),

swaps as (

select *

from optimism.core.fact_event_logs

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

),

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,

case when event_inputs:toToken='0x4200000000000000000000000000000000000006' then abs(event_inputs:outputAmount*pow(10,-6))

when event_inputs:toToken='0x7f5c764cbc14f9669b88837ca1490cca17c31607' then abs(event_inputs:inputAmount*pow(10,-6))

end as swap_vol,

case when router_address='0xb7c1cca934641da45f84eda0cf06af73f4a5a523' then 'ZeroSwap'

when router_address='0x1111111254760f7ab3f16433eea9304126dcd199' then '1inch'

when router_address='0xdef1abe32c034e558cdd535791643c58a13acc10' then 'Matcha'

when router_address='0x6352a56caadc4f1e25cd6c75970fa768a3304e64' then 'OpenOcean'

when router_address='0x00c0184c0b5d42fba6b7ca914b31239b419ab80b' then 'SlingShot'

when router_address=lower('0xdef171fe48cf0115b1d80b88dc8eab59176fee57') then 'ParaSwap'

else 'na' end as address_name

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,address_name

from combine

group by aggregator, router_address,address_name