date_trunc('day', block_timestamp) as dt,

direction,

sum(amount_usd) as vol_usd,

count(distinct tx_id) as tx_count,

case

when direction = 'IN' then 'swap_from_$PSP'

when direction = 'OUT' then 'swap_to_$PSP'

else null end as swap_type,

case

when direction = 'IN' then (vol_usd*-1)

else vol_usd end as swap_volume_usd

from ethereum.dex_swaps

token_address = lower('0xcafe001067cdef266afb7eb5a286dcfd277f3de5')

group by 1,2)

select dt, swap_type, tx_count, swap_volume_usd

from raw