select

case when from_address like '0x%' then from_address else native_to_address end as from_address,

count(*) as thor_freq,

sum(from_amount_usd + to_amount_usd) / 2 as thor_volume

from flipside_prod_db.thorchain.swaps

where from_address like '0x%'

or native_to_address like '0x%'

group by 1

),

eth_swap as (

select

from_address,

thor_freq,

thor_volume,

count(*) as eth_freq,

sum(case when amount_in_usd is not null then amount_in_usd else amount_out_usd end) as eth_volume

from ethereum.core.ez_dex_swaps

inner join thorswap on (origin_from_address = from_address)

group by 1, 2, 3

select

*,

thor_volume + eth_volume as total_volume,

avg(total_volume) over () as avg_volume,

thor_freq + eth_freq as total_freq,

avg(total_freq) over () as avg_freq

from eth_swap

where thor_freq > 100

and eth_freq > 100