swaps as (

select

*,

abs(amount1_adjusted) as volume,

case

when amount0_adjusted > 0 then '$wBTC to $USDC'

when amount0_adjusted < 0 then '$USDC to $wBTC'

else null

end as swap_direction

from

ethereum.uniswapv3.ez_swaps

where

pool_address = '0x99ac8ca7087fa4a2a1fb6357269965a2014abc35'

and date(block_timestamp) >= current_date - 31

order by

block_timestamp desc

),

address as (select tx_hash, from_address

from ethereum.core.fact_transactions

where tx_hash in (select tx_hash from swaps)),

topswapper as (select a.block_timestamp, a.tx_hash, a.swap_direction, a.volume, b.from_address

from swaps a

left join address b on a.tx_hash = b.tx_hash)

select

sum(volume) as total_volume,

count(distinct tx_hash) as total_swap,

avg(volume) as average_swap,

count(distinct from_address) as total_address,

(total_swap/total_address) as swap_per_wallet,

(total_volume/total_address) as average_amount_per_wallet

from topswapper