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 date_trunc('day', block_timestamp) as dt, swap_direction, count(distinct from_address) as traders, sum(volume) as volume_usd

from topswapper

group by 1,2