SELECT

sum(CASE when AMOUNT0_ADJUSTED > 0 then AMOUNT0_ADJUSTED end) as volume_in,

avg(CASE when AMOUNT0_ADJUSTED > 0 then AMOUNT0_ADJUSTED end) as avg_vol_in,

sum(CASE when AMOUNT0_ADJUSTED < 0 then AMOUNT0_ADJUSTED end) as volume_out,

avg(CASE when AMOUNT0_ADJUSTED < 0 then AMOUNT0_ADJUSTED end) as avg_vol_out,

count(DISTINCT tx_hash) AS n_txns,

count(DISTINCT sender) as n_swappers,

volume_in-volume_out as total_volume,

volume_in+volume_out as net_volume,

CASE when net_volume <0 then 'a' else 'b' end as colour

FROM ethereum.uniswapv3.ez_swaps

WHERE pool_address = {{p}}'0x88e6a0c2ddd26feeb64f039a2c41296fcb3f5640'

AND block_timestamp::date BETWEEN CURRENT_DATE- interval '{{last_period}} , 1 day' AND CURRENT_DATE-1